Most robust way to parse CSV file via Apex code

Question

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

Answer 1

Update: Winter '23

With Winter '23 there is now the Developer Preview of directly using DataWeave in Apex - See Use DataWeave in Apex to Enable Data Transformation to Different Formats (Developer Preview).

This provides support for extracting and transforming CSV data in Apex and outputting it in the required format, including directly mapping into sObjects.

The feature isn't GA yet, but will provide a path forward where you don't need to implement your own parser and deal with the specifics of the file format.

[Updated in my role as the Apex Product Manager at Salesforce]

---

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.

Answer 2

First of all, I agree with the previous answer from @Daniel Ballinger.

Here I would like to present a solution that can cover most situations when we have to work with small and simple CSV files. This solution supports quotes and commas in the calls. The example shows only how to parse rows by APEX test. Two key functions are used here: String.split and String.unescapeCsv. The solution does not touch line breaks.

@isTest
static void test_parseCsv_basicMethods() {
    String exampleRow = 'one,"t,wo",""three"",fou""",r""","""fiv,e"""';
    String[] csvCallsData = exampleRow.trim().split(',(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)');

    Assert.areEqual(5, csvCallsData.size());
    Assert.areEqual('one', csvCallsData[0]);
    Assert.areEqual('"t,wo"', csvCallsData[1]);
    Assert.areEqual('""three""', csvCallsData[2]);
    Assert.areEqual('fou""",r"""', csvCallsData[3]);
    Assert.areEqual('"""fiv,e"""', csvCallsData[4]);

    Assert.areEqual('one', csvCallsData[0].unescapeCsv());
    Assert.areEqual('t,wo', csvCallsData[1].unescapeCsv());
    Assert.areEqual('"three"', csvCallsData[2].unescapeCsv());
    Assert.areEqual('"fiv,e"', csvCallsData[4].unescapeCsv());

    Assert.areEqual('fou""",r"""', csvCallsData[3].unescapeCsv());
}