Remove formatting from file

Jan 15, 2014 at 3:07 PM
I'm using the AsDataSet() method to import large Excel files into DataSets. It performs extremely well until I hit a file that has a lot of formatting. With formatting, I cancelled after ~ 30 minutes. I then opened the Excel file, removed all formatting, and it worked almost instantly.

I'm using .xls files, not .xlsx.

I might have the option of removing the formatting when they are generated, but it would be a lot easier for me if I could somehow make then work, whether it's by telling ExcelDataReader to ignore the formatting or by somehow removing it before processing.

Any ideas?
Developer
Jan 20, 2014 at 8:35 AM
That is interesting. What was the formatting? dates etc? or graphical mostly?
Jan 20, 2014 at 2:25 PM
Text color, text font type, text size. When I highlighted all and said "Remove Formatting" it took seconds to import. I was dealing with 50,000+ rows, but not many columns.

The source of the Excel file was an export from Business Objects, so the formatting was applied in the report.


Developer
Jan 21, 2014 at 8:48 AM
How many rows was it?


Developer
Jan 21, 2014 at 8:48 AM
Sorry just saw that you said 50000+


Developer
Jan 27, 2014 at 8:59 AM
I tried creating a 60000 row sheet with formatting, but it ran quite quickly. Was there a lot of different formats throughout the file? Was it tabular data? Were rows varying in format?
Jan 27, 2014 at 1:36 PM
Interesting. I'll look deeper at the file. It was created from Business Objects so there might have been some other garbage in play. I'll check to see if it's proprietary data also, and if not then maybe I'll send a copy.


Developer
Jan 29, 2014 at 1:14 PM
That would be good