Thursday, August 06, 2009

PC Support for Oracle

So I'm working with Oracle Support on an Oracle Applications R12 issue. They want to see some data from one of our tables and give me a query to run. I run the query in SQL Developer and export the results as a .csv file and upload it to the TAR.

Three days later they get back to me and say they can't open the files. Hmm, that's funny, let me try the same thing.

I load it into Open Office in about 2 seconds, no problem.

Then I load it into a database using SQL*Loader with no issues.

Same thing with MySQL and LOAD DATA, no issues.

I mail the file to myself and again it works no problem.

I send the file to a collegue and Open Office works for them as well.

The support analyst asks for a plain .xls worksheet so I try to load it into MS Excel. Bingo, I get an error:

This error is usually encountered when an attempt to open a file with more than 65,536 rows or 256 columns is made. Excel is limited to 65,536 rows of data and 256 columns per worksheet. You can have many worksheets with this number of rows and columns, but they are usually capable of fitting into one workbook (file). The number of worksheets you can have per workbook is limited only by the amount of available memory your system has. By default, Excel can manage 3 worksheets, more if there is available memory to support the quantity of data.

Truncation of rows or columns in excess of the limit is automatic and is not configurable. This issue can usually be remedied by opening the source file with a text editor, such as Microsoft Office Word, and then saving the file off into multiple files with row or column counts within the limits of an Excel worksheet. These files can then be opened or imported into Excel worksheets.

If you are using a data format that does not support use of a text editor, it may be easier to import the data into Microsoft Office Access and then use the export feature of Access to import the data to an Excel format. Other methods of importing large source material into multiple worksheets are available, but may be more complex than using either a text editor or Access.

Aha! MS Excel can't handle anything with over 256 columns of data and my data is 325 columns! (Not my design, it comes from Oracle Applications).

I post my findings to the TAR and suggest they load the data into a piece of software that can handle 325 columns, like maybe Oracle. (I didn't have the heart to suggest MySQL, but I guess they wouldn't take that as a slight anymore ...)


Joel Garry said...

Jeez, you'd think support analysts would understand the concept of saying what you've done that results in an error. Wait, who am I kidding?

word: fuzler

David Aldridge said...

Latest version of excel supports a lot more rows. But you do get a funky "ribbon" thing instead of a proper menu. Weird.

Damien said...

I guess Oracle hasn't heard of Excel 2007! What a shame.