Question Re: Datawindow SaveAs generates large Excel file in Technology Q&A

Datawindow SaveAs generates large Excel file

Former Member — Tue, 21 Mar 2017 15:49:32 GMT

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]

Re: Datawindow SaveAs generates large Excel file

Former Member — Tue, 21 Mar 2017 22:55:58 GMT

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

Re: Datawindow SaveAs generates large Excel file

Former Member — Wed, 22 Mar 2017 15:05:48 GMT

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.

Re: Datawindow SaveAs generates large Excel file

Former Member — Mon, 27 Mar 2017 14:21:04 GMT

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.