0

My requirement is that I need to create new records / update existing records based on the data from an email attachment consisting of .csv file format that is parsed to the Email Service that I've created. But I'm unable to figure it how to do this process which might be helpful to upload data via an email. If there is any piece of code which meets my requirement pls pass it to me.

Thanks in advance

This is my email service code can you help me on this because when I parse email containing the csv file attachment to the particular sObject the debug logs show null stating that there is no attachment in my Email

global class SupplierCapacityDataImportEmail implements Messaging.InboundEmailHandler { global Messaging.InboundEmailResult handleInboundEmail(Messaging.InboundEmail email, Messaging.Inboundenvelope envelope) {

    Messaging.InboundEmailResult result = new Messaging.InboundEmailResult();
    Messaging.InboundEmail.textAttachment[] tAttachments = email.TextAttachments;
    System.debug('Text Attachement from the email.' + tAttachments);
    Messaging.InboundEmail.BinaryAttachment[] bAttachments = email.BinaryAttachments;
    System.debug('Binary Attachement from the email.' + bAttachments);
    String csvbody = '';

    If(bAttachments != Null)
    {
        For(Messaging.InboundEmail.binaryAttachment btt : bAttachments)
        {
            System.debug('This is a Binary Attachement from the email parsed.'+ btt);
            If(btt.Filename.endsWith('.csv'))
            {
                System.debug('Binay Attachment' + btt);
                csvbody = btt.body.toString();
                System.debug('Battachment' + csvbody);
            }
        }
    }
    Else If(tAttachments != Null)
    {
        For(Messaging.InboundEmail.textAttachment ttt : tAttachments)
        {
            System.debug('This is a  Text Attachment from the email parsed.' + ttt);
            If(ttt.Filename.endsWith('.csv'))
            {
                System.debug('Text Attachment' + ttt);
                csvbody = ttt.body;
                System.debug('TXTAttachment' + csvbody);
            }
        }  
    }
    return result;
}

}

  • Here is one way to parse CSV in Apex. – Keith C Oct 5 '15 at 7:52
  • Will it be able to create and save new records by populating data from the csv file attachment. @Keith C – Nemesis88 Oct 5 '15 at 8:08
  • You will have to add code to take the output of the parseCSV method and do appropriate type conversion and create the populated record. – Keith C Oct 5 '15 at 9:09
  • @ Keith C Is there no other way to do via the email service rather than the parseCSV method that you have mentioned above. – Nemesis88 Oct 5 '15 at 9:32
  • I don't know of another way - someone else may. – Keith C Oct 5 '15 at 12:03
1

My New code to read and insert data from a csv file using the salesforce email service

Global class CSVFileDataImportEmail implements Messaging.InboundEmailHandler
{
    Global string nameFile{get;set;}
    Global blob contentFile{get;set;}
    String[] filelines = new String[]{};
        Boolean processSheet = True;
    List<Supplier_Capacity__c> SCupload;    
    List <Messaging.SingleEmailMessage> Mail = new List <Messaging.SingleEmailMessage>();
    List <Messaging.SingleEmailMessage> Mails = new List <Messaging.SingleEmailMessage>();
    Global Messaging.InboundEmailResult handleInboundEmail(Messaging.InboundEmail email, Messaging.Inboundenvelope envelope)
    {
        nameFile = email.toString();         
        filelines = nameFile.split('\n');
        System.debug(' filelines... ' + filelines);
        SCupload = new List<Supplier_Capacity__c>();
        //Here I'm writing a map since I've 2 lookup fields
        Map<String,String> SUP_map = New Map<String, String>();
        Map<String,String> SH_map = New Map<String, String>();
        String a1= '';
        for(Account A:[Select Id,Name From Account])
        {
            System.debug(' ~Supplier~ ' + A.ID + A.Name);  
            SUP_map.put(A.Name,A.ID);
            System.debug(' SUP_map ' + SUP_map);
            System.debug(' SUP_mapID ' + SUP_map.get(A.ID) + ' SUP_mapName ' + SUP_map.get(A.Name));
        }
        for(Site__c S:[Select id, Site_Code__c From Site__c])
        {
            System.Debug(' <Ship From> '+ S.ID + S.Site_Code__c);
            SH_map.put(S.Site_Code__c,S.ID);
            System.debug(' SH_map ' + SH_map);
            System.debug(' SH_mapID ' + SH_map.get(S.ID) + ' SH_mapName ' + SH_map.get(S.Site_Code__c));
        }

        Messaging.InboundEmailResult result = new Messaging.InboundEmailResult();
        System.debug(' >email< ' + email);
        String fileAttachments = '';

        System.debug(' email attachments ' + email.TextAttachments);
        Messaging.InboundEmail.textAttachment[] tAttachments = email.TextAttachments;
        System.debug(' size of tAttachments ' + tAttachments.size() + 'Text Attachement from the email.' + tAttachments);
        Messaging.InboundEmail.BinaryAttachment[] bAttachments = email.BinaryAttachments;
        System.debug(' Binary Attachement from the email. ' + bAttachments);
        String csvbody = '';
        for(integer i=0; i < tAttachments.size(); i++)
        {
            System.debug(' attacments... ' + tAttachments[i]);
            fileAttachments = String.valueOf(tAttachments[i]);
        }
        List<Supplier_Capacity__c> supc = new List<Supplier_Capacity__c>();

        List<String> badrows = new List<String>();
        String csvBody1 = '';
        String[] lineByCsvBody = new String[]{};
            List<String> linebycsvdata = new List<String>();
        If(tAttachments != Null && tAttachments.size() > 0)
        {
            For(Messaging.InboundEmail.textAttachment ttt : tAttachments)
            {
                csvBody1 = ttt.body;
                lineByCsvBody = csvBody1.split('\n');
                System.debug('CSV BODY'+ csvBody1 + 'lines' +lineByCsvBody +' size of lineByCsvBody ' + lineByCsvBody.size() + ' This is a Text Attachment from the email parsed. ' + ttt);

                For(Integer j = 0; j < lineByCsvBody.size(); j++)
                {
                    System.debug(' lineByCsvBody data...... ' + lineByCsvBody[j]);
                    If(j==0)
                    { 
                        /*Here I'm checking and splitting the header values*/
                        String headerRow = lineByCsvBody[j];
                        System.debug(' headerRow DATA ...  ' + headerRow);
                        String hvalue = csvbody1;
                        String[] headervalue = headerRow.split(',');
                        System.debug(' size of headervalue ' + headervalue.size());
                        System.debug(' headervalue1 '+ headervalue[0] + ' headervalue2 ' + headervalue[1]
                                    + ' headervalue3 ' + headervalue[2] + ' headervalue4 ' + headervalue[3]
                                    + ' headervalue5 ' + headervalue[4] + ' headervalue6 ' + headervalue[5]);

                        if(headervalue[0] == Null || headervalue[0] == '' || headervalue[0].trim().length() < 1)
                        {
                            badrows.add('Column 1 : Supplier Column name is null.');
                        }    
                        Else if(headervalue[0] != 'Supplier')
                        {
                            badrows.add('Column 1 : Supplier Column name should be accurate.');
                        }

                        IF(headervalue[1] == Null || headervalue[1] == '' || headervalue[1].trim().length() < 1)
                        {
                            badrows.add('Column 2 : Ship From Column name is null.');
                        }
                        Else if(headervalue[1] != 'Ship From')
                        {
                            badrows.add('Column 2 : Ship From Column name should be accurate.');
                        }

                        IF(headervalue[2] == Null || headervalue[2] == '' || headervalue[2].trim().length() < 1)
                        {
                            badrows.add('Column 3 : Capacity Column name is null.');
                        }
                        Else if(headervalue[2] != 'Capacity')
                        {
                            badrows.add('Column 3 : Capacity Column name should be accurate.');
                        }

                        IF (headervalue[3] == Null || headervalue[3] == '' || headervalue[3].trim().length() < 1)
                        {
                            badrows.add('Column 4 : Lead Time Column name is null.');
                        }
                        Else if(headervalue[3] != 'Lead Time')
                        {
                            badrows.add('Column 4 : Lead Time Column name should be accurate.');
                        }

                        IF (headervalue[4] == Null || headervalue[4] == '' || headervalue[4].trim().length() < 1)
                        {
                            badrows.add('Column 5 : Part unique to Buyer Column name is null.');
                        }
                        Else if(headervalue[4] != 'Part unique to Buyer') 
                        {
                            badrows.add('Column 5 : Part unique to Buyer Column name should be accurate.');
                        }
                        String headervalue5=headervalue[5].trim();
                        System.debug('headervalue5 is '+ headervalue[5] +' for last row');
                        IF (headervalue[5] == Null || headervalue[5] == '' || headervalue[5].trim().length() < 1)
                        {
                        badrows.add('Column 6 : Shared Resource Column name is null.');
                        }
                        Else if(headervalue5 != 'Shared Resource') 
                        {   
                            System.debug('headervalue[5] '+ headervalue[5] + ' size... '+headervalue[5].length() + 'hardcode resource size ' + headervalue5.length());
                            badrows.add('Column 6 : Shared Resource Column name should be accurate.');                            
                        }

                        System.debug(' badrows ' + badrows.size());
                        /*Here you can either put up your own result.message(Error Message) or messaging.singleemailmessage to send your own errror email*/
                        /*result.message = ('The following Column No: ' +   badrows.size() + ' failed due to mismatching of header values in the csv parsed to the Email Service.');*/

                        Messaging.SingleEmailMessage Errormail = new Messaging.SingleEmailMessage();

                        List <String> sendTo = new List <String>();
                        sendTo.add('bhuvarahan.vln@thirdware.com');
                        Errormail.setToAddresses(sendTo);

                        Errormail.setReplyTo('bhuvarahanvln@gmail.com');
                        Errormail.setSenderDisplayName('Supplier Capacity');

                        List <String> ccTo =  new List <String>();
                        ccTo.add('siddharthar.t@thirdware.com');
                        ccTo.add('ashwinibharat.balantrapu@tspl.com');
                        Errormail.setCcAddresses(ccTo);

                        Errormail.setSubject('ERROR :: Failed to process Capacity Upload template (CSV)');

                        String body = '<html><body>Dear Supplier,' + '<br>' + '<br>' +'The Capacity Upload CSV Template failed to process because the below header values are missing in the sheet.' + '<br>' + '<br>';

                        if(badrows.size() > 0)
                        {
                            processSheet = false;
                            for(String str:badrows)
                            {
                                System.debug('value of str ' + str);
                                body += str + '<br>' +'<br>';
                            }
                        }
                        body += '<br>' + '<br>' + 'Correct the header columns and re-send for uploading again.' 
                                + '<br>' + 'Reach out to  Admin for further assistance.' 
                            + '<br>' + '<br>' + '<br>' + '-  Team.</body></html>';

                        Errormail.setHtmlBody(body);

                        Mail.add(Errormail);

                        System.debug(' size of list ' + lineByCsvBody.size());
                        System.debug(' processSheet ' + processSheet);
                        if(badrows.size() > 0)
                        {
                            Messaging.sendEmail(Mail);
                            break;
                        }
                    }
                    if(j>0 && processSheet)
                    {
                        linebycsvdata.add(lineByCsvBody[j]);
                        System.debug('inside j>0'+linebycsvdata);
                    }
                }
            }
        }
        System.debug(' size of list ' + lineByCsvBody.size());
        System.debug(' size of list of linebycsvdata ' + linebycsvdata.size());
        System.debug(' processSheet ' + processSheet);
        if( processSheet ) 
        {
            System.debug('inside insertion part ' + linebycsvdata.size());
            Try
            {

                integer rowCount=0;
                for(String svalue:linebycsvdata)
                {
                    /*Here I'm checking and splitting the values / data contained in the .csv file*/
                    rowCount++;
                    String[] splitvalue = svalue.split(','); 
                    System.debug(' size of splitvalue ' + splitvalue.size());
                    Supplier_Capacity__c customobject = new Supplier_Capacity__c();
                    System.debug(' splitvalue1 ' + splitvalue[0] + ' splitvalue2 ' + splitvalue[1]
                                 + ' splitvalue3 ' + splitvalue[2] + ' splitvalue4 ' + splitvalue[3]
                                 + ' splitvalue5 ' + splitvalue[4] + ' splitvalue6 ' + splitvalue[5]);
                    if(splitvalue[0] == '' || splitvalue[0].trim().length() < 1 || splitvalue[0] == Null)
                    {
                        badrows.add('row number'+rowCount+' field value is missing');
                        continue;
                    }
                    if(splitvalue[1] == '' || splitvalue[1].trim().length() < 1 || splitvalue[1] == Null)
                    {
                        badrows.add('row number'+rowCount+' field value is missing');
                        continue;
                    }
                    if(splitvalue[2] == '' || splitvalue[2].trim().length() < 1 || splitvalue[2] == Null)
                    {
                        badrows.add('row number'+rowCount+' field value is missing');
                        continue;
                    }
                    if(splitvalue[3] == '' || splitvalue[3].trim().length() < 1 || splitvalue[3] == Null)
                    {
                        badrows.add('row number'+rowCount+' field value is missing');
                        continue;
                    }
                    if(splitvalue[4] == '' || splitvalue[4].trim().length() < 1 || splitvalue[4] == Null)
                    {
                        badrows.add('row number'+rowCount+' field value is missing');
                        continue;
                    }
                    if(splitvalue[5] == '' || splitvalue[5].trim().length() < 1 || splitvalue[5] == Null)
                    {
                        badrows.add('row number'+rowCount+' field value is missing');
                        continue;
                    }
                    String chk = SUP_map.get(splitvalue[0]);
                    String chk1 = SH_map.get(splitvalue[1]);
                    system.debug(' check_map ' + chk + splitvalue[0]);
                    system.debug(' check_map1 ' + chk1 + splitvalue[1]);

                    customobject.Account__c = SUP_map.get(splitvalue[0]);                       
                    customobject.Ship_From__c = SH_map.get(splitvalue[1]);
                    customobject.Capacity__c = Decimal.valueof(splitvalue[2]);
                    customobject.Lead_Time__c = Decimal.valueof(splitvalue[3]);
                    customobject.Part_unique_to_Lear__c = Boolean.valueof(splitvalue[4]);
                    customobject.Shared_capacity__c = Boolean.valueof(splitvalue[5]);
                    insert customobject;   
                    supc.add(customobject);
                    System.debug(' passed record size ' + supc.size());
                }
                For(string u:badrows)
                {
                    a1 += u;
                }
                System.debug(' Badrows ' + a1);
                result.success = true;
                System.debug(' Result '  + result.success);         
            }
            catch (Exception e)
            {
                System.debug(' Exception ' + e);
                result.success = false;
                System.debug(' Result '  + result.success);
            }
            Messaging.SingleEmailMessage SuccessMail = new Messaging.SingleEmailMessage();

            List <String> sendTo1 = new List <String>();
            sendTo1.add('bhuvarahan.vln@thirdware.com');
            SuccessMail.setToAddresses(sendTo1);

            SuccessMail.setReplyTo('bhuvarahanvln@gmail.com');
            SuccessMail.setSenderDisplayName('Supplier Capacity');

            List <String> ccTo1 =  new List <String>();
            ccTo1.add('siddharthar.t@thirdware.com');
            ccTo1.add('ashwinibharat.balantrapu@tspl.com');
            SuccessMail.setCcAddresses(ccTo1);

            SuccessMail.setSubject('Supplier Capacity Upload :: Success :: '+ supc.size() + ' records. ' + 'ERROR :: ' + badrows.size() + ' records.');

            String body1 = '<html><body>Dear Supplier,' + '<br>' + '<br>' + 'Number of Records sucessfully processed :: '+ supc.size() + '<br>' + '<br>' + 'Number of Records failed to process :: ' + badrows.size() + '<br>' + '<br>' + '<br>' + '- Team.</body></html>';
            /*result.message = ('The following number of records '+ supc.size() + ' got created successfuly by the Email Service and ' + '<br>' + a1 + ' number of records got failed by the Email Service.');*/

            SuccessMail.setHtmlBody(body1);

            Mails.add(SuccessMail);
            if(supc.size() > 0)
            {
                Messaging.sendEmail(Mails);
            }
        }
        return result;
    }
}

Not the answer you're looking for? Browse other questions tagged or ask your own question.