I'm writing a service that imports large spreadsheets into a database table. If I start with a CSV file, then I can use

FileReadLine

(with a bit of fancy handling) to process one row, without having to read the whole file into memory first. And then I can parallelize the row processing to speed the whole thing up. This part all works wonderfully. But now I'm trying to adjust it to handle xlsx files too. My basic plan was to convert the xlsx file to a csv via:

cfspreadsheet(action: "read", src: filePath, format: "csv", name: "local.csvString");
FileWrite(filePath, local.csvString);

and then just pass the CSV file to my previously mentioned code. But what I'm finding is that ColdFusion is throwing an OutOfMemoryError. It's getting caught and logged by my onError function in Application.cfc. But I'm trying to figure out how to catch it before that, and give users a better error message. Wrapping the cfspreadsheet in a try/catch is not doing the trick. So is there something else I can try? I'm also willing to take ideas on how to more efficiently process these files.

Have you checked this out? https://blog.simplicityweb.co.uk/140/processing-large-spreadsheet-files-efficiently-in-cfml (CF25 has better handling for large CSVs as well, if I recall.)

I'm stuck on CF2021 for this though, so CF2025 things won't help me. 😞 But looking at this library now. It looks like it does what I'm already doing, so porting my code over to it will probably be easy.

I will second spreadsheet-cfml, it almost always works better for me for all spreadsheet stuff. and definitely works better on large ones.

Here's the related CSV details https://blog.simplicityweb.co.uk/138/reading-large-csv-files-with-cfml

Thirding spreadsheet-cfml, just installed last week, and the developer hangs out here and is very helpful!

fourthing 🙂