0

I am trying to recieve a email with an attachment (CSV FIle) and have it iterate through the csv and upsert it as records in a custom object. i upload the document by hand daily. PAIN!

i am using a csv parsing class i have found in Salesforce Code Samples that makes the body of the csv into a multidimensional List. which is great but i dont know how to properly iterate through the list data without nesting too many for loops inside each other. which i dont want to do.

Global Messaging.InboundEmailResult handleInboundEmail(Messaging.InboundEmail email, Messaging.InboundEnvelope env) {

    Messaging.InboundEmail.BinaryAttachment[] bAttachments = email.BinaryAttachments; 
    string csvBody = null;  
    list<list<string>> parsedCsvData = new list<list<string>>();
    List<Wholesale_Pipeline__c> lstPipe = new List<Wholesale_Pipeline__c();

    String mySubject = email.subject.toLowerCase();
    String s = 'Byte Report';
    Boolean reportName;
    reportName = mySubject.contains(s);



    if (reportName == true) {

        try {
            if(bAttachments != null){
                for(Messaging.InboundEmail.BinaryAttachment btt : bAttachments){
                    if(btt.filename.endsWith('.csv')){
                        csvBody.btt.body.toString();
                    }
                }
                if(csvBody != null){
                    parsedCsvData = parseCSV.ParseCSV(csvBody, true);
                }
                if(!parsedCsvData.isEmpty()){
                    for(list<list<string>> parsedLoop : parsedCsvData){

                    }
                }
            }
        }
    }                    
}    

if i add: inside the first loop to iterate

for(string s1 : parsedLoop){    
    for(integer i = 0; i < parser.size(); i++){
       Wholesale_Pipeline__c pipe = new Wholesale_Pipeline__c();
       pipe.name = s1[0];
       lstPipe.add(pipe);
    }
 }

it just seems to excessive to add that many loops inside each other, if that is the only way then i will do so. but can anyone help and maybe show me another technique of how i can accomplish iterating these lists. as well as updating records with these lists.

CSV Parser:

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;       
}
3

Yes, you're going to need two loops. That's the price you pay for having to parse a two-dimensional list. The trick is to put the record you're creating outside the inner loop. However, since you also have a header row (typically), you usually want to remove that one first.

String[] headers = parsedCsvData.remove(0);
Wholesale_Pipeline__c[] records = new Wholesale_Pipeline__c[0];
for(String[] row: parsedCsvData) {
  Wholesale_Pipeline__c record = new Wholesale_Pipeline__c();
  for(Integer index = 0, size = headers.size(); index < size; index++) {
    // Simplified, you need to actually parse the data types
    record.put(headers[index], row[index]);
  }
  records.add(record);
}
insert records;
  • i edited the post to include the csv parser – Bryan Cerrati Mar 22 '18 at 16:59
  • i can have the parser keep the headers, but they will in no way match the salesforce field names. so can i create a dummy header that will match the fields? and if so how can i go about that? – Bryan Cerrati Mar 22 '18 at 17:00
  • i dont understand what im supposed to do? – Bryan Cerrati Mar 22 '18 at 19:06
  • @BryanCerrati if they don't match, you'll need to arrange for a way the user or something to make the fields – sfdcfox Mar 22 '18 at 20:28
  • how can i set a static header? – Bryan Cerrati Mar 22 '18 at 20:41

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.