Parse csv email attachment with comma in data

Question

I have a comma delimited csv file that some of the fields may contain comma. I found this code, https://github.com/forcedotcom/CustomMetadataLoader/blob/master/custom_md_loader/classes/CSVFileUtil.cls

However, i don't quite follow how to tell the opening and closing double quote, and how to concatenate the split fields between them together. Can anyone help?

Sample file data is as:

Registrar Name,IANA ID,Primary Email,Primary First Name,Primary Last Name "ABC Hongkong Holoding,INC",12345,[email protected],"Siu, Lau",Cheung

Answer 1

XL Zhang,

Here's one approach for splitting a CSV... it will work for the use case you mentioned (i.e. embedded commas).

    public static void importCSV() {

        // Create a dummy CSV for testing.
        // Note: This code does not handle a case where the column begins or
        // ends with embedded quotes (e.g. """This Fails"",Column 2, Column 3).
        String csv = 'Name,City,State,Description\n"St,ev,ens, Skip",New York,NY,"This is the ""description"" field for Skip Stevens."\n"Jones, Bob",San Francisco,CA,"This is the ""description"" field for Bob Jones."\n"Smith, Mary",Chicago,IL,"This is the ""description"" field for Mary Smith."\n';

        // Create a new list of lists to hold the CSV information.
        List<List<String>> csvList = new List<List<String>>();

        // Split the string into rows and loop through them.
        // You may need to change the end-of-line character(s) depending on your data.
        for (String row : csv.split('\n')) {

            // Create a list to hold all of the columns for this row.
            List<String> rowList = new List<String>();

            // Setup a temporary column. This will hold the "pieces" of a split string.
            String tempCol = '';

            // Setup a flag for whether or not we're within a quote.
            Boolean inQuote = false;

            // Split the row into columns based on a comma.
            // This will split on ALL commas, even those within double-quotes.
            // We'll put the double-quoted ones together again below.
            for (String col : row.split(',')) {

                // Unescape the field. This will take care of embedded double-quotes.
                col = col.unescapeCsv();

                if (col.startsWith('"')) {

                    // If this column starts with a quote, we're at the beginning of
                    // an embedded comma series of columns.
                    inQuote = true;

                    // Include this portion of the string in our temporary column.
                    tempCol = col + ',';

                } else if (col.endsWith('"')) {

                    // If this column ends with a quote, we're at the end of
                    // an embedded comma series of columns.
                    inQuote = false;

                    // Include this portion of the string in our temporary column.
                    tempCol += col;

                    // Remove the quotes.
                    tempCol = tempCol.replace('"','');

                    // Output the temporary column.
                    rowList.add(tempCol);

                } else if (inQuote) {
                    // If we're within a quote, simply concatenate the current column
                    // to our temporary column.
                    tempCol += col + ',';
                }
                else {
                    // If none of the previous conditions are true, we have a simple
                    // column that can be output immediately.
                    rowList.add(col);
                }

            }

            // Append this row to the overall CSV list.
            csvList.add(rowList);

        }

        // Check the output.
        system.debug(csvList);

    }