How to Parse a CSV in Salesforce

Question

I have a CSV that contains fields that need to go to several Salesforce custom objects. I've created the APEX class to handle the parsing. However, when I call the class from a VisualForce page, I receive the message:

Collection size 3,511 exceeds maximum size of 1,000.

My APEX class is as follows (cobbled together from 2 blog posts on the topic):

public class parseCSV{  
public Blob contentFile { get; set; }    
public String nameFile { get; set; }    
public Integer rowCount { get; set; }    
public Integer colCount { get; set; }    
public List<List<String>> getResults() {        
 List<List<String>> parsedCSV = new List<List<String>>();        
 rowCount = 0;        
 colCount = 0;        
 if (contentFile != null){            
 String fileString = contentFile.toString();            
 parsedCSV = parseCSV(fileString, false);            
 rowCount = parsedCSV.size();            
 for (List<String> row : parsedCSV){                
 if (row.size() > colCount){                    
 colCount = row.size();                
}            
}        
}        
return parsedCSV;    
}  
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;       
}  
}

How do I ensure that the above class inserts the correct columns from the CSV into the proper custom object?

Since some objects contain lookups to others, how do I ensure that data is loaded into the lookup fields first, and then into the child tables?

Finally, How do I work around the error message above? Is something besides use of a VisualForce page suggested?

Thanks much for your help and guidance.

Answer 1

Out of interest, is there a requirement for this to be from a visualforce page? You could just use Data Loader to load CSVs, or even build a solution using the bulk API yourself.

If you do need to do it from a page, I'd suggest that rather than splitting on a new line immediately, you use substring and the indexOf to pull just one line at a time from the input string, processing them as you go.