1

I have been tasked to create a batch apex loader which parses a CSV file from an email attachment.

I will use an email handler to get the email with the CSV file attached, and then I will try to load the attachment, and go through it, and create a new object for each line in it.

The CSV file will be sent from an outside source, and it can contain a lot of long text in some of the columns. Even line-breaks, and quotes can appear in some of the columns.

Is there a good, readily available CSV parser class/library in APEX, that I could use? One that handles all the cases, where there could be separators, quotes, end of lines INSIDE a value? Also, I would like to be able to handle all kinds of international & special characters.

I don't want to re-invent the wheel, and spend a lot of time on writing & testing the parser, if there's something already out there.

Thanks

  • 1
    note that some of the csv parsers mentioned below may have trouble on large files due to SFDC limitations on regex string sizes or heap. So if you're getting files with thousands of rows, be sure to test on the largest size file you will receive – cropredy Sep 10 '19 at 18:12
  • Given that CSV's have a reasonably simple format defined in RFC 4180 it can be surprising complicated to actually implement a parser that is both correct to the spec and efficient for larger CSV files. Support for possibly embedded line breaks, double quotes, and commas makes it more complicated. – Daniel Ballinger Mar 4 at 21:32
1

CSV parsing doesn't need to be complicated, as it is a very simple file format. You could look at ones like this or this, etc. The only three special cases you have to deal with are quoted fields (which includes newlines, seperators, etc), double quotes (escaped quotes), and normal fields. If you've got more than 100 lines of code, you're likely doing too much work. Note that you will be limited to using UTF-8, though, as Apex does not allow other character encoding for strings. There's absolutely no way around this limitation other than binary parsing, which presumably is something you'd like to avoid.

| improve this answer | |
  • I think the specification is deceptively complicated. For example, the first example given RT_CSVReader currently has problems with nested double quotes and embedded line breaks. – Daniel Ballinger Mar 4 at 21:35
  • @DanielBallinger is there a robust repo that you recommend? – PartOfTheOhana Jul 24 at 19:14
  • @PartOfTheOhana I'm using a modified version of RT_CSVReader. It isn't perfect, but it mostly gets the job done. You just need to be very aware of the limitations. Both in terms of what features are supported and how much data you can process before you hit limits. – Daniel Ballinger Jul 27 at 0:14
1

Given that CSV's have a reasonably simple format defined in RFC 4180 it can be surprising complicated to actually implement a parser that is both correct to the spec and efficient for larger CSV files.

Support for possibly embedded line breaks, double quotes, and commas makes it more complicated. There isn't an easy way to divide the workload up in Apex because you can't split the incoming CSV into multiple lines safely. The only way to tell if a line-break or comma is actual data or part of the CSV format is to parse right up to it.

If you want to parse a larger CSV file that leaves you juggling CPU and Heap limits. Maybe Salesforce Functions will help in this scenario, but as at today I don't believe there is a robust way to do this for CSV files that start to get into the thousands of lines of records. E.g. 4 MB plus of data.

Of course, with Apex being multi-tenant there is always going to be an upper limit on the amount of data you can work with in one transaction. But as I mentioned above, there isn't an easy way to break a CSV down into manageable chunks unless you impose limits on how the CSV data is formatted.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.