A customer tried to export a larger found set (around 15500 records) to excel. The script uses FM's Save Records As Excel []. While it works well with smaller data sets (tried up to 2000 - 3000). it hands back an empty excel file after a longer while with that larger data set. The script runs fin until that step, collecting all data into a single export table.
Are there any undocumented, yet know limitations regarding record numbers for excel export?
Files hosted on FMS 19.2
Client on MacOS, FMP 19.2
Connection: LAN
Did you look at the excel sheet to make sure it didn't have garbage data or some other problem? 15K rows is really not very large. Assuming the sheet is OK after inspection, I would post a problem report.
Thanks for looking into this, @OliverBarrett!
I am exporting CSV now. it takes nearly an hour to get the 15k records in a file (about 20 fields). It is the legacy part of the solution and a couple of calc fields are involved. I now suspect them being the source of this slowness and somehow this may lead to the empty excel file.
It is still slow, Oliver's post confirms that FM's Excel export works fine.
However, it also confirmed my suspicion, that the unstored calc fields bog the export I'm dealing with down.
It is not a Save Record as Excel [] issue but a known calc field side-effect. His answer makes this clear. I think now the empty excel file is caused the client going in sleep mode and been thrown out by the server, not receiving any more data from the host ( I am still investigation server logs).
I don't know what the limit is, but there's definitely a limit.
Anecdote
I happened across a mention of a csv file, freely downloadable, which contained all the info to let you determine distance between any two US addresses based on their ZIP codes. (At least within the limitations of the granularity of ZIP codes). Without much further contemplation I thought that might be a useful module. Downloaded the file. Double-clicked it to see the column titles and get a sense of what I'd just acquired. Excel could not open it. I assume it would have done no better if the file in question had been an .xlsx file instead of a .csv file. Because I aimed BBEdit at it. BBEdit is text editor supremo on the Mac. BBEdit couldn't open the damn thing either. Might have something to do with the fact that it was a 38 GB file or something along those lines. Downloaded a text editor that said it was designed for handling humongous log files, an app called Glogg. Glogg was able to open it. 1,098,491,594 rows.
Just for the challenge and practice I decided to import it into FileMaker. Cuz you never know when you'll be tasked with importing a billion-plus record csv file into FileMaker, right? Didn't want to just aim it at the file and kick off a multi-hours-long process I couldn't interrupt and resume later, so the goal was to split this beastie into pieces beforehand. Used the command line after searching out the syntax. Then made a looping script that would let me pause between segments if I had the Caps Lock key down. FileMaker can apparently handle a lot more records than Excel (or even more lines of text than BBEdit) and I ended up with this relatively useless, albeit functional, 84.37 GB FileMaker database file. (There are algorithms that let you calculate distance without needing a whale-sized database, but it does work. The fields are indexed. I can do a find for any zip code and then enter a second zip in a global and via relationship it fetches the distance between. More to the point, if I ever have to contend with a data dump of this size for which there's no available replacement algorithm, I'm prepared).
So... somewhere south of a billion ninety eight million records is a number above which you probably can't export to Excel format and expect anything to be able to open the result.
That's way above the 15k record I am dealing with. Thank you for sharing this with us, @AHunter3.
I had the case where an xml import had 30k+ lines and ended up splitting it manually into smaller chunks and let FM import the files subsequently, which drove the total import time down, compared to importing a single big file.
I think the long execution time for the task I described in the OP is due to unstored calc fields. I saved the table into a new FM file in order to get rid of the calcs. It took nearly as long as a CSV export. However, exporting the 15k records from this new FM file with no calc fields to excel took merely 3 seconds, with coincides with @OliverBarrett' s observation.