Datawindow SaveAs generates large Excel file

[Former Member]

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]

[Former Member · Accepted solution]

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.

[Former Member]

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.

[Former Member]

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