0

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,slc@abcholding.hk,"Siu, Lau",Cheung

3
  • Could you provide a description or example of the CSV file? If the file is consistently structured (e.g. every column has double-quotes around it, etc.) the String class' split method with a regular expressions may do what you need. Sep 3, 2019 at 21:50
  • You should ask the source to wrap strings with quotes if they have commas (or other special characters that impact processing). Also see: tools.ietf.org/html/rfc4180 Sep 4, 2019 at 10:11
  • i added a sample data. Yes. by default the comma delimited csv file has double quotes to wrap the field data if the field has comma
    – XL Zhang
    Sep 4, 2019 at 12:37

1 Answer 1

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);

    }

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.