processing xls file in Salesforce

Question

I need to process xls files and create records from them, automatically, in APEX of flow. I know there is no native salesforce solution for it, what is the best way of doing so?

Answer 1

Tl;dr: The best way to do this in Salesforce is to not do it in Salesforce

The best way is probably to use a third party service instead of Salesforce (flows, Apex, etc...)

Salesforce isn't a platform for general computing, and the governor limits make it difficult to work with all but the most basic of file formats (and xls/xlsx are complex beasts) even if you find/make some Apex that is able to parse them.

xlsx, for example, is basically a fancy .zip file, and Zippex can unzip that, but anything larger than about 34 kB (suggested by issue #16¹ on the github repo) runs into the CPU limit; and that's just getting at the xml, not even parsing the xml.

Even as little as 500 rows with 4 integer columns, 3 email columns, and 3 general string columns is likely going to exceed that.

Using a dataweave script in Apex may be a better approach than trying to do something with Apex alone, but that specifically cannot handle xlsx. It does, however, appear to support the older xls format (which is a binary format).

Even if you can use Dataweave (or the Compression.ZipReader in developer preview at time of writing mentioned by sfdcfox), the CPU and Heap Space governor limits will dictate the size of data you can feasibly work with. Going off-platform (or possibly to Heroku) is going to mean you have a lot fewer restrictions.

^{1: There was some research done into making the blob->int conversion faster with pr #20, but it doesn't look like that work ever made it into a release. Even if you found a fork that did use that, it's at most an 18x speedup, meaning you'd be able to unzip up to about a 620 kB file. That's still without processing it into records, though.}