Skip to Content
0

Datawindow SaveAs generates large Excel file

Mar 21, 2017 at 03:49 PM

175

avatar image
A V

Hello,

I have a datawindow which retrieves more than 500K rows. I need to save as an excel spreadsheet. I am calling the SaveAs function - dw_1.SaveAs (ls_file, xlsx!, true).

This works, but the resulting file has a size of about 600MB. If I open the file in Excel (version 10) and then save it as a new file, the new file has a size of about 37MB.

Anyone has any idea what I am missing here? My PB version is 12.5.2 [5609]

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
A V Mar 27, 2017 at 02:21 PM
0

Sorry... I meant to type Excel 2010 and not Excel 10. It looks like Excel 2010 uses some kind of compression. I ended up writing code to open the xlsx file in excel and then saving it once again.

Share
10 |10000 characters needed characters left characters exceeded
John Fauss Mar 21, 2017 at 10:55 PM
0

You didn't state how many rows are in the 2nd file, but if you really are using Excel XP (which is version 10) then I would venture a guess that you are running into the limit of 65,535 rows. Are you able to try this with a recent version of Excel? You might additionally try to perform the SaveAs and produce a Comma-Separated Value (CSV) file as an additional test to see if your version of Excel 10 can handle that.

HTH

John

Share
10 |10000 characters needed characters left characters exceeded
Roland Smith Mar 22, 2017 at 03:05 PM
0

Excel XP has a limit of 65,536 rows and 256 columns. Excel 2007 and newer are limited to 1,048,576 rows and 16,384 columns.

The file saved from Excel is smaller because it was truncated.

Share
10 |10000 characters needed characters left characters exceeded