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!  

Generating random password for contacts

If you have any application in which you want only your salesforce contacts to log in. You can create log in page and authenticate contacts by giving password to them. For that purpose you need set Contact’s password and send it to their email address and show password as encrypted field on layout so that admin can’t see it. Here’s what we need to do.

Create Custom Button and call webservice to generate password and send to its email address.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66

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

The e-mail deliverability is a new feature released in spring 13 and as per the release notes, all newly refreshed sandboxes default to "system email only"

The access level might have been set to "system e-mails".

In setup | Administration setup | Email Administration | Deliverability, verify if the access level is set to "All e-mails".

Integration with Salesforce using REST API

REST API provides a powerful, convenient, and simple Web services API for interacting with Force.com.
Its advantages include ease of integration and development, and it’s an excellent choice of technology for use with mobile applications and Web 2.0 projects

A Lot of people ask about how can we integrate external system with Salesforce without using any ETL tool. Here is the example in which I am going to show you how can we insert Account in salesforce by using .NET windows form application.

First, we need to create connected app that uses OAuth protocol to verify both the Salesforce user and the external application.

Follow these steps to create a connected app:
From Setup, click Create | Apps.
In the Connected Apps section, click New.

When you’ve finished entering the information, click Save to save your new app.If you’re using OAuth, saving your app gives you two new values the app uses to communicate with Salesforce:

Consumer Key: A value used by the consumer to identify itself to Salesforce. Referred to as client_id in OAuth 2.0.
Consumer Secret: A secret used by the consumer to establish ownership of the consumer key. Referred to as client_secret in OAuth 2.0

We can Authenticate the user via one of several different OAuth 2.0 authentication flows. In this example we are using Username-password flow, where the application has direct access to user credentials.

User Interface of the application from which we are inserting Account record.

Code Snippet.

 using System;  
 using System.Collections;  
 using System.Collections.Generic;  
 using System.ComponentModel;  
 using System.Data;  
 using System.Drawing;  
 using System.Linq;  
 using System.Text;  
 using System.Windows.Forms;  
 using System.Web.Script.Serialization;  
 using Newtonsoft.Json;  
 using Newtonsoft;  
 using System.Net;  
 using System.Web;  
 using System.IO;  
 using System.Configuration;  
 using System.Collections.ObjectModel;  
 using System.Dynamic;  
 namespace SalesforceREST  
 {  
   public partial class MainForm : Form  
   {  
     // Consumer Key from SFDC account  
     private string clientID = "YOUR CLIENT ID";  
     // Consumer Secret from SFDC account  
     private string clientSecret = "YOUR CLIENT SECRET";  
     // Redirect URL configured in SFDC account  
     private string redirectURL = "resttest:callback";  
     private string sfUserName = "SFDC USERNAME";  
     private string sfPassword ="SFDC PASSWORD";  
     private string sfSecurityToken = "SFDC SECURITY TOKEN";  
     private TokenResponse token;   
     public MainForm()  
     {  
       InitializeComponent();  
     }  
     private void MainForm_Load(object sender, EventArgs e)  
     {    
       // Build authorization URI  
       var authURI = new StringBuilder();  
       authURI.Append("https://test.salesforce.com/services/oauth2/authorize?");  
       authURI.Append("response_type=code");  
       authURI.Append("&client_id=" + clientID);  
       authURI.Append("&redirect_uri=" + redirectURL);  
     }  
     private void GetToken()  
     {    
       // Create the message used to request a token  
       string URI = "https://test.salesforce.com/services/oauth2/token";  
       StringBuilder body = new StringBuilder();  
       body.Append("grant_type=password&");  
       body.Append("client_id=" + clientID + "&");  
       body.Append("client_secret=" + clientSecret + "&");  
       body.Append("username=" + sfUserName + "&");  
       body.Append("password=" + sfPassword + sfSecurityToken);  
       string result = HttpPost(URI, body.ToString());  
       // Convert the JSON response into a token object  
       JavaScriptSerializer ser = new JavaScriptSerializer();  
       token = ser.Deserialize<TokenResponse>(result);  
       var uri = token.instance_url + "/services/data/v20.0/sobjects/Account/";  
       var acct = new sfdcAccount();  
       acct.Name = AccountName.Text;  
       acct.Type = AccountType.Text;  
       acct.AnnualRevenue = AnnualRevenue.Text;  
       acct.Fax = Fax.Text;  
       acct.Phone = Phone.Text;  
       var serr = new JavaScriptSerializer();  
       var bodyy = serr.Serialize(acct);  
       var reeq = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(uri);  
       reeq.Headers.Add("Authorization: OAuth " + token.access_token);  
       reeq.ContentType = "application/json";  
       reeq.Method = "POST";  
       byte[] data = System.Text.Encoding.ASCII.GetBytes(bodyy);  
       reeq.ContentLength = bodyy.Length;  
       var oss = reeq.GetRequestStream();  
       oss.Write(data, 0, data.Length);  
       oss.Close();  
       WebResponse resp;  
       try  
       {  
         resp = reeq.GetResponse();  
       }  
       catch (WebException ex)  
       {  
         resp = ex.Response;  
       }  
       string test;  
       if (resp == null) test = null;  
       System.IO.StreamReader sr = new System.IO.StreamReader(resp.GetResponseStream());  
       test = sr.ReadToEnd().Trim();   
       // Read the REST resources  
       string s = HttpGet(token.instance_url + @"/services/data/v20.0/", "");  
       UIResults.Text = s;  
       UIResults.Text = result;  
       UIResults.Text = test;  
     }  
     public string HttpPost(string URI, string Parameters)  
     {  
       System.Net.WebRequest req = System.Net.WebRequest.Create(URI);  
       req.ContentType = "application/x-www-form-urlencoded";  
       req.Method = "POST";  
       // Add parameters to post  
       byte[] data = System.Text.Encoding.ASCII.GetBytes(Parameters);  
       req.ContentLength = data.Length;  
       System.IO.Stream os = req.GetRequestStream();  
       os.Write(data, 0, data.Length);  
       os.Close();  
       // Do the post and get the response.  
       System.Net.WebResponse resp = req.GetResponse();  
       if (resp == null) return null;  
       System.IO.StreamReader sr = new System.IO.StreamReader(resp.GetResponseStream());  
       return sr.ReadToEnd().Trim();  
     }  
     public string HttpGet(string URI, string Parameters)  
     {  
       System.Net.WebRequest req = System.Net.WebRequest.Create(URI);  
       req.Method = "GET";  
       req.Headers.Add("Authorization: OAuth " + token.access_token );  
       System.Net.WebResponse resp = req.GetResponse();  
       if (resp == null) return null;  
       System.IO.StreamReader sr = new System.IO.StreamReader(resp.GetResponseStream());  
       return sr.ReadToEnd().Trim();  
     }  
     private void InsertAccountBtn_Click(object sender, EventArgs e)  
     {  
       GetToken();  
       clearall();  
     }  
     public void clearall()   
     {  
       AccountName.Text = "";  
       AccountType.Text = "";  
       AnnualRevenue.Text = "";  
       Fax.Text = "";  
       Phone.Text = "";  
     }  
   }  
   public class TokenResponse  
   {  
     public string id { get; set; }  
     public string issued_at { get; set; }  
     public string refresh_token { get; set; }  
     public string instance_url { get; set; }  
     public string signature { get; set; }  
     public string access_token { get; set; }  
   }  
   public class sfdcAccount  
   {  
     public string Name { get; set; }  
     public string Type { get; set; }  
     public string AnnualRevenue { get; set; }  
     public string Phone { get; set; }  
     public string Fax { get; set; }  
   }  
 }  

The record is successfully created in Salesforce. You can verify the ids from above error log and URL.

Feel free to post questions and suggestions. Cheers !  

Populating date from one apex:inputText to another using Javascript

If you want to set new date on apex:inputText by referencing date from other element you can do it by using javascript. You don't need to use apex date methods.

 function functionreadOnly(Index,SecondElementId){  
      var idToDate;  
      var valueToDate;  
      if(Index != 0){  
           idToDate = SecondElementId.replace(Index + ":SecondElementId",Index-1 + ":FirstElementId");  
           valueToDate = document.getElementById(idToDate).value;  
           var Obj = document.getElementById(SecondElementId);  
           var dayAfter = new Date(valueToDate);   
           dayAfter.setDate(dayAfter.getDate()+1);  
           var finalEndDate = (dayAfter.getMonth() + 1) + '/' + dayAfter.getDate() + '/' + dayAfter.getFullYear() ;  
           Obj.value = finalEndDate;        
           //Making element read only  
           if(Obj.value.length != 0){  
                Obj.readOnly = true   
           }  
      }  
 }  

Required Mark on Input Text

If you are using inputText the red bar doesn't come with it. You have to consider inputField and make it required to show the red bar. If you want to show red bar with inputtext then you have to use requireInput css. Below is the code snippet.

 apex:pageBlockSectionItem>  
     <apex:outputLabel value="Name on Card"/>  
         <apex:outputPanel>  
             <div class="requiredInput">  
                 <div class="requiredBlock"></div>  
                     <apex:inputText value="{!name}" label="Name on Card" required="true"/>  
                 </div>  
             </div>  
         </apex:outputPanel>  
 </apex:pageBlockSectionItem>  

Custom Cloning of Object using dynamic SOQL

If you want to clone custom object along with its child objects then standard won't work. Standard cloning only clone the object record itself and does not clone its child objects.

In order to fulfil that requirement you have create custom clone button and invoke a webservice. Javascript which needs to be written to call webservice is not included in this example.

Standard Clone method only copy the fields which are queried from object and as you know fields may change during developmemt so in order to make a solution flexible and not dependent on fields we can make dynamic SOQL for quering the fields.

The below example will clone object (Clone_Object__c) record along with its child (Clone_Object_Child__c) record and redirect the user to new cloned record without having dependency of fields.

Code Snippet

 // Class is global because of webservice  
 global class CloneClass {  
      // Returns a dynamic SOQL statement for the whole object, includes only creatable fields since we will be inserting a cloned result of this query  
      public static string getCreatableFieldsSOQL(String objectName, String whereClause){  
           String selects = '';  
           if (whereClause == null || whereClause == ''){ return null; }  
           // Get a map of field name and field token  
           Map<String, Schema.SObjectField> fMap = Schema.getGlobalDescribe().get(objectName.toLowerCase()).getDescribe().Fields.getMap();  
           list<string> selectFields = new list<string>();  
           if (fMap != null){  
                for (Schema.SObjectField ft : fMap.values()){ // loop through all field tokens (ft)  
                     Schema.DescribeFieldResult fd = ft.getDescribe(); // describe each field (fd)  
                     if (fd.isCreateable()){ // field is creatable  
                          selectFields.add(fd.getName());  
                     }  
                }  
           }  
           if (!selectFields.isEmpty()){  
                for (string s:selectFields){  
                     selects += s + ',';  
                }  
                if (selects.endsWith(',')){selects = selects.substring(0,selects.lastIndexOf(','));}  
           }  
           return 'SELECT ' + selects + ' FROM ' + objectName + ' WHERE ' + whereClause;  
      }  

      // Clone method and redirect the user to cloned record.  
      public String clone(String cloneObjId){  
           String soql = CloneClass.getCreatableFieldsSOQL('Clone_Object__c','id=\''+cloneObjId+'\''); // Static method is called by class  
           Clone_Object__c cloneObj = (Clone_Object__c')Database.query(soql);  
           Clone_Object__c newcloneObj = cloneObj.clone(false, true);  
           newcloneObj.Name = 'Clone-'+ cloneObj.Name;  
           insert newcloneObj;  
           List<Clone_Object_Child__c> lstcloneObjChild = new List<Clone_Object_Chld__c'>();  
           String soqlCloneObjChild = CloneClass.getCreatableFieldsSOQL('Clone_Object_Child__c'','Clone_Object__c'=\''+cloneObjId+'\'');  
           List<Clone_Object_Child__c> lstcloneObjChild = (List<Clone_Object_Child__c'>)Database.query(soqlCloneObjChild);  
           for(Clone_Object_Child__c cloneObjChild : lstcloneObjChild){  
                Clone_Object_Child__c newcloneObjChild = cloneObjChild.clone(false);  
                newcloneObjChild.Clone_Object__c = newcloneObj.id;  
                newcloneObjChild.Is_default__c = false;  
                lstcloneObjChild.add(newcloneObjChild);  
           }  
           insert lstcloneObjChild;  
           return String.valueOf( new PageReference('/'+newcloneObj.Id+'/e').getUrl() + '?retURL='+newcloneObj.Id); //This lands on the new cloned record n Edit Mode.   
      }

      // Webservice which is called by custom button  
      webservice static String deepClone(String aid){  
           Clone_Object__c cloneObj = [Select Id,Name, FROM Clone_Object__c' WHERE Id =: aid]; // aid : Id of record which we want to clone  
           CloneClass cloneClassObj = new CloneClass(cloneObj);  
           String p = cloneClassObj.clone(aid); // Calling class clone method and passing Id of record  
           return p;  
      }  
 }    

Formatting date field in apex:inputtext

If we would like to have the ability to use a date field in Visualforce without relying on a salesforce or custom object then we can acheive this by using <apex:inputtext>. Below is the code snippet

Visualforce Page:

 <apex:page controller="inputTextDemo" id="mypage">  
      <span class="dateInput dateOnlyInput">  
           <apex:inputText id="inputFieldId" size="12" value="{!DateStringProperty}" style="width:72px;text-align:center;" onclick="DatePicker.pickDate(false, this, false);" onfocus="DatePicker.pickDate(false, this, false);"/>  
      </span>  
 </apex:page>  

Controller:

 public class inputTextDemo{  
      // String Property which is bind to <apex:inputtext>  
      public String DateStringProperty{get;set;}   
      public inputTextDemo(){  
           // Populating field value by today date.  
           DateStringProperty = Date.Today().format(); // Populating field value by today date.  
      }  
      public void insertDateMethod(){  
           Custom_Object__c obj = new Custom_Object__c();  
           // Since value is string and Date field does not take string value so we need to parse this string as Date  
           obj.DateField__c = Date.parse(DateStringProperty);   
           insert obj;  
      }  
 }