Import: Excel file source - bug?

June 13, 2011 9:53AM

I have just been trying to import an Excel spreadsheet and have found some strange behaviour:

The sheet has 86 rows of data and 4061 columns which need to be transposed. When I import the sheet via the [File] block I get no error message but it only imports 500 fields (columns) and all 86 records (rows).

I then tried changing the [Reader] from 'Excel as CSV' to 'Excel direct'. This then gives me a warning message headed 'Row/column limit':
"It is not possible to import more than 676 columns and 1048576 records. Your excel document has 4061 columns and 86 records. Your data has been truncated to fit the limits"

So 3 questions really:
1 - Why is there a '676' limit when Excel 2010 allows 16384 columns?
2 - Why does it only import 500 columns NOT 676?
3 - Why do you not get a warning when using the 'Excel as CSV' option (also the same for the 'Omniscope' [Reader]?

chris · June 13, 2011 10:29AM

Hi Ed,

Thanks for the questions.

1. The limit is enforced by the Excel direct reader. The method employed by the Excel direct reader does not allow us to import more than 676 columns and 1048576 records.

2/3. The Excel CSV reader works by opening the spreadsheet in Excel and then saving it as CSV before importing it into Omniscope. I'm assuming that when Excel saves the data as CSV it can only save the first 500 fields. You can easily test this by opening the file yourself and saving the data as CSV. Please let me know if you are able to save all the fields using this method. Can you import all the data using the Omniscope reader?

June 13, 2011 10:34AM

Chris,

Excel exported all 4061 columns to csv without any complaint.

Omniscope reader was unable to import anything above 500 columns.

Thanks,
Ed

chris · June 13, 2011 10:46AM

Hi Ed,

Can you try changing "Settings>Advanced>Misc>Limit CSV/TSV Import to 500 fields" and let me know if that works.

June 13, 2011 1:30PM

Chris,

Unticked the setting and still only get 500 columns with all 3 readers

chris · June 14, 2011 4:19AM

Ed,

This works for me. Make sure you restart Omniscope after changing the setting, then select "Block options>Update data" to force a re-read of the data.

June 14, 2011 4:39AM

Chris,

I hadn't forced the data to update but now I have and have the following to report:

- Omniscope reader --> all 4061 columns picked up
- Excel direct reader --> 676 columns picked up
- Excel as CSV reader --> all 4061 columns picked up

Thanks for the help!

Welcome!

Categories

Ideas Parade

Tagged