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]
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.
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
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.