August 14, 2012

Parsing CSV to sObject using Apex in Salesforce

To parse CSV to sObject using Apex in Salesforce, use the below link to get the code

http://wiki.developerforce.com/page/Code_Samples#Parse_a_CSV_with_APEX

Sample code:

  1. public List<List<String>> parseCSV(String contents,Boolean skipHeaders) {    
  2.   
  3.      List<List<String>> allFields = new List<List<String>>();      
  4.      // replace instances where a double quote begins a field containing a comma  
  5.      // in this case you get a double quote followed by a doubled double quote    
  6.      // do this for beginning and end of a field    
  7.  
  8.      contents = contents.replaceAll(',"""',',"DBLQT').replaceall('""",','DBLQT",');    
  9.   
  10.      // now replace all remaining double quotes - we do this so that we can reconstruct 
  11.      // fields with commas inside assuming they begin and end with a double quote     
  12.   
  13.      contents = contents.replaceAll('""','DBLQT');    
  14.  
  15.      // we are not attempting to handle fields with a newline inside of them  
  16.      // so, split on newline to get the spreadsheet rows     
  17.   
  18.      List<String> lines = new List<String>();      
  19.   
  20.      try {    
  21.   
  22.          lines = contents.split('\n');    
  23.   
  24.      }  catch (System.ListException e) {    
  25.   
  26.          System.debug('Limits exceeded?' + e.getMessage());    
  27.   
  28.      }     
  29.   
  30.      Integer num = 0;      
  31.   
  32.      for(String line : lines) {    
  33.   
  34.          // check for blank CSV lines (only commas)      
  35.   
  36.          if (line.replaceAll(',','').trim().length() == 0) break;               
  37.   
  38.          List<String> fields = line.split(',');          
  39.          List<String> cleanFields = new List<String>();    
  40.          String compositeField;  
  41.          Boolean makeCompositeField = false;    
  42.   
  43.   
  44.          for ( String field : fields ) {    
  45.   
  46.              if (field.startsWith('"') && field.endsWith('"')) {    
  47.   
  48.                  cleanFields.add(field.replaceAll('DBLQT','"'));    
  49.   
  50.              } else if (field.startsWith('"')) {    
  51.   
  52.                  makeCompositeField = true;  
  53.                  compositeField = field;    
  54.   
  55.              } else if (field.endsWith('"')) {    
  56.   
  57.                  compositeField += ',' + field;    
  58.                  cleanFields.add(compositeField.replaceAll('DBLQT','"'));    
  59.                  makeCompositeField = false;    
  60.   
  61.   
  62.              } else if (makeCompositeField) {    
  63.   
  64.                  compositeField +=  ',' + field;    
  65.   
  66.              } else {    
  67.   
  68.                  cleanFields.add(field.replaceAll('DBLQT','"'));    
  69.   
  70.              }    
  71.   
  72.          }              
  73.   
  74.          allFields.add(cleanFields);    
  75.   
  76.      }    
  77.   
  78.      if (skipHeaders) allFields.remove(0);      
  79.   
  80.      return allFields;          
  81.   
  82. }   

Here contents is fileContent.toString(). Where fileContent is the value of file mentioned in <apex:inputFile.>

Example:

<apex:inputFile value = "{!fileContent}" name = "{!fileName}"/>

2 comments:

  1. can you explain what contents = contents.replaceAll(',"""',',"DBLQT').replaceall('""",','DBLQT",'); does? How will this make the comma inside the double quote to be ignored when split using comma as delimiter?

    ReplyDelete
    Replies
    1. contents = contents.replaceAll(',"""',',"DBLQT').replaceall('""",','DBLQT",'); will replace all the double quotes (if present) in the column to DBLQT.

      Delete