Wednesday 27 May 2015

Email Service to Parse CSV

Howdy. I had requirement for parsing CSV in email service, thought I should post it on my blog.So lets get started.

Step :1

First you need to create Apex class that acts as an inbound email handler which simply needs to implement the Messaging.InboundEmailHandler interface. The interface defines a single method which is handleInboundEmail.

Code Snippet: 
global class myHandler implements Messaging.InboundEmailHandler {
    
    global Messaging.InboundEmailResult handleInboundEmail(Messaging.InboundEmail email, Messaging.InboundEnvelope envelope) {
    Messaging.InboundEmailResult result = new Messaging.InboundEmailresult();
    Messaging.InboundEmail.BinaryAttachment[] tAttachments = email.binaryAttachments; // // Since Attachment is CSV we are taking as BinaryAttachments. 
    list<Account> lstAccount = new list<Account>(); // list of accounts.
    String csvbody='';
    
    list<list<String>> allFields = new list<list<String>>(); // list of rows in csv
    list<String> lines = new list<String>(); // Rows of CSV
    list<String> headers = new list<String>(); // Field names   
    list<String> fields = new list<String>(); // list of fields 
    
    if(tAttachments !=null){
        for(Messaging.InboundEmail.BinaryAttachment btt : tAttachments){
            if(btt.filename.endsWith('.csv')){
                csvbody = btt.body.toString();//Take the blob body of the CSV binary attachment and extract it to a string object, then process it.
                try {
                    // 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 
                    csvbody = csvbody.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 
                    csvbody = csvbody.replaceAll('""','DBLQT');
                    lines = csvbody.split('\n');
                }catch (System.listException e){
                    System.debug('Limits exceeded?' + e.getMessage());
                }
            }
        }
        
        integer rowNumber = 0;
        
        for(String line : lines) {
        // check for blank CSV lines (only commas) 
            if (line.replaceAll(',','').trim().length() == 0) break;
                fields = line.split(',', -1);
                allFields.add(fields);
                if(rowNumber == 0){
                    // for getting the field names. 
                    for(list<String> rows : allFields){    
                        for(String header : rows){
                            headers.add(String.valueof(header.trim()));
                        }   
                    break;
                    }
                
                    rowNumber++;
                    continue;
                }
        
            }
        
            for(Integer i = 1 ; i < lines.size() ; i++){
                Account a = new Account();
                a.put(headers[0] , allFields[i][0]);
                a.put(headers[1] , allFields[i][1]);
                a.put(headers[2] , allFields[i][2]);
                lstAccount.add(a);
            }
            insert lstAccount;
        }
        return result;
    }
}
Step 2:

Next, you need to define the email service. This establishes an email address, which you can then tie to the Apex class that you've just written. Define the email service by logging into your environment.
  • Log into Force.com and select on Setup > App Setup > Develop > Email Services
  • Select New Email Service
After setting up your email service, you'll see something like this 


That's it. So simple isn't it :) To test your service, send email to the email address which is specified in your email service as below


After the sending email you will see Account created in your Salesforce Org (if you have setup everything correctly).


Note: This class assumes that you have three column in your excel sheet as below


You can extend the functionality as per your columns in excels. Please feel free to comment if you have any questions. 


Happy Coding. Cheers!  

No comments:

Post a Comment