Learn From My Mistake:
We have a line of business system that is difficult to “pull” data from. I did, however, discover that it has the ability to generate and email data extracts on a schedule. Combine this with the ability to email files to our FME Flow Server, and the job became doable. The only minor hang-up was that the system could only generate these extracts in XLSX format. “No problem,” a more naive me thought, “I can use an Excel reader to pull this tabular data out easily!”
The process worked great for our smaller datasets (< 10k rows) and ran most jobs in less than a minute, so I began exporting more data this way. Fast forward a little bit, and I discover that some of my newest extracts are taking up to 4 HOURS to process. Slightly panicked, I disable the extracts and dig-in. Looking at the logs, the workspace first seems to be lagging on a PythonCaller I was using to massage the data after extraction, so I re-tool it into using vanilla transformers. Test locally - works great. Deploy - 4 hour run. 😔
It’s then I realize that it’s the Excel reader (and Flow’s allocated memory) that’s choking on the 400k rows of data in the sheet as it builds a Document Object Model in memory of it. With a little AI help, however, I was able to create a short Python script that would take the XLSX file, unzip it for the XML files therein, and quickly dump the rows & columns to a CSV file. I then ran this CSV through my FeatureReader, and the workspace continued as normal. Test locally - works great. Deploy - 40-second run for the file that used to take our server 4 hours.
My mistake: Pay attention to your Flow server’s job stats, especially when it triggers memory reallocation messages. And don’t just process Excel files as they are if you don’t have to. As I learned (20+ years into my career as a developer) they’re just zipped-up XML files. 😂
