Oh my god. It's full of code!

Salesforce Apex CSV Parsing to sObject

So for a recent project I’ve been working on, a person wants to be able to email in a small CSV file to an automated bot. The bot will extract the attachment, iterate through the data and import the data. So of course first I had to get together a CSV parser. Thankfully that was already handled by the awesome people over at the developer force wiki. So first, grab their Apex CSV parser. So that returns a list of a list of strings. Each list entry is a list of the values in that row. So now while this is nice, it’s still not idea for importing. You need to convert that data into an sObject, or a list of them. So I drafted this little function that works in tandem with the first one. You can pass it that data you get from the first parser, and the type of sObject you want to serialize the data into and it will give you back a list of sObjects. Some things to note about this function.

  • You must pass in the column headers as the first row
  • The column headers must match the API names of the fields. So your column headers must match the sObject field names
  • The parser can deal with invalid field names. It will simple not set them on the sObject. So you can include extra data, it will just be discarded
  • I havn’t tested it much. It may fail with some data types, but I’m not sure
//Name: parseCSV
//Description: Class for parsing and casting CSV content to sObjects
//Author: Daniel Llewellyn and blog post at http://www.preludeinteractive.com/2009/09/parsing-csv-files-in-apex/ (author unknown)
//Date: December 1 2011
public class parseCSV
{   
    //code borrowed from http://www.preludeinteractive.com/2009/09/parsing-csv-files-in-apex/
    public static List<List<String>> parseCSV(String contents,Boolean skipHeaders)
    {
        List<List<String>> allFields = new List<List<String>>();
    
        // replace instances where a double quote begins a field containing a comma
        // in this case you get a double quote followed by a doubled double quote
        // do this for beginning and end of a field
        contents = contents.replaceAll(',"""',',"DBLQT').replaceall('""",','DBLQT",');
        // now replace all remaining double quotes - we do this so that we can reconstruct
        // fields with commas inside assuming they begin and end with a double quote
        contents = contents.replaceAll('""','DBLQT');
        // we are not attempting to handle fields with a newline inside of them
        // so, split on newline to get the spreadsheet rows
        List<String> lines = new List<String>();
        try {
            lines = contents.split('\n');
        } catch (System.ListException e) {
            System.debug('Limits exceeded?' + e.getMessage());
        }
        Integer num = 0;
        for(String line : lines) {
            // check for blank CSV lines (only commas)
            if (line.replaceAll(',','').trim().length() == 0) break;
            
            List<String> fields = line.split(',');  
            List<String> cleanFields = new List<String>();
            String compositeField;
            Boolean makeCompositeField = false;
            for(String field : fields) {
                if (field.startsWith('"') && field.endsWith('"')) {
                    cleanFields.add(field.replaceAll('DBLQT','"'));
                } else if (field.startsWith('"')) {
                    makeCompositeField = true;
                    compositeField = field;
                } else if (field.endsWith('"')) {
                    compositeField += ',' + field;
                    cleanFields.add(compositeField.replaceAll('DBLQT','"'));
                    makeCompositeField = false;
                } else if (makeCompositeField) {
                    compositeField +=  ',' + field;
                } else {
                    cleanFields.add(field.replaceAll('DBLQT','"'));
                }
            }
            
            allFields.add(cleanFields);
        }
        if (skipHeaders) allFields.remove(0);
        return allFields;       
    }
 
    public static list<sObject> csvTosObject(List<List<String>> parsedCSV, string objectType)
    {
        Schema.sObjectType objectDef = Schema.getGlobalDescribe().get(objectType).getDescribe().getSObjectType();
        system.debug(objectDef);
        
        list<sObject> objects = new list<sObject>();
        list<string> headers = new list<string>();
        
        for(list<string> row : parsedCSV)
        {
            for(string col : row)
            {
                headers.add(col);
            }
            break;
        }
        system.debug('========================= File Column Headers');
        system.debug(headers);
            
        integer rowNumber = 0;
        for(list<string> row : parsedCSV)
        {
            system.debug('========================= Row Index' + rowNumber);
            if(rowNumber == 0)
            {
                rowNumber++;
                continue;
            }
            else
            {
                sObject thisObj = objectDef.newSobject();
                integer colIndex = 0;
                for(string col : row)
                {                   
                    string headerName = headers[colIndex].trim();
                    system.debug('========================= Column Name ' + headerName);
                    if(headerName.length() > 0)
                    {                  
                        try
                        {                       
                            thisObj.put(headerName,col.trim());
                        }
                        catch(exception e)
                        {
                            system.debug('============== Invalid field specified in header ' + headerName);                           
                        }
                        colIndex++;
                    }
                } 
                objects.add(thisObj);
                rowNumber++;
            }       
        }
        return objects;
    }
    @isTest
    public static void testParseCSV()
    {
        string csvContent = 'firstname,lastname\nTest,Guy';
        List<List<String>> parsedContent = parseCSV(csvContent,false);
        list<contact> contactList = (list<contact>) csvTosObject(parsedContent, 'contact');
        system.assertEquals(contactList.size(),1);
        system.assertEquals(contactList[0].firstname,'Test');
        system.assertEquals(contactList[0].lastname,'Guy');
    
    }    
}

If nothing else this should at least be a jumping off spot for anyone trying to work with CSV files in Apex. If you make any improvements, I’d love to see/hear them. Hope this helps someone!

8 responses

  1. Anonymous

    Thanks for this. I used it to do a CSV email service. How about testing? Could you offer any advice for a test class? Thanks again!

    April 1, 2013 at 3:32 pm

    • Glad it helped. Writing a test class should be pretty easy. Just pass it a CSV that represents a contact or something and assert that it comes back with the fields set properly. I can write something up real quick. I’ll modify the post shortly.

      April 1, 2013 at 3:34 pm

      • Thanks Kenji!

        April 1, 2013 at 4:00 pm

      • Subhan

        Hi,

        Did u got a chance to write test class for the above request. If yes, can you please post your test class code here?

        April 9, 2015 at 9:15 am

  2. Ashish

    thanks..it really helped

    August 21, 2013 at 3:54 pm

  3. Balkishan Kachawa

    Hi Thanks.It really helped but how to parse CSV more then 1MB in size because it fails when size is more than 1 or 2 MB.

    May 23, 2014 at 8:07 am

  4. Anonymous

    Method does not exist or incorrect signature: parseCSV(String, Boolean)

    February 1, 2017 at 1:36 pm

  5. Balajee

    Method does not exist or incorrect signature: parseCSV(String, Boolean) Please replay why i am getting this error!!

    February 1, 2017 at 1:37 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s