function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
mahi1mahi1 

Import Contacts from Excel Sheet Using Visualforce Page

Hi All,

        How to Import Contacts from Excel Sheet Using Visulaforce page


Thanks in advance
Best Answer chosen by mahi1
sandeep sankhlasandeep sankhla
Hi Mahi,

You can use CSV parser for this..plese refer the below example code where I have created one Visualforce page from where I am uplaoding the excel file .csv and extracting the records data and mapping with object columns

please check and let me know if it helps you

page:

<apex:page sidebar="false" controller="UploadOpportunityScheduleLineItem">
    <apex:form >
        <apex:sectionHeader title="Upload data from CSV file"/>
          <apex:pagemessages />  
          <apex:pageBlock >
            <center>
                <apex:inputFile value="{!contentFile}" filename="{!nameFile}" />
                <apex:commandButton action="{!ReadFile}" value="Upload File" id="theButton" style="width:70px;"/>
                <br/> <br/>
            </center>  
      
      <apex:pageBlocktable value="{!mapUnmatchedSchedules}" var="mapID"  title="Unmathed Lines of CSV file">
          <apex:column headervalue="Opportunity Id">
              {!mapUnmatchedSchedules[mapID][0]}
          </apex:column>
          <apex:column headervalue="Oportunnity Name">
              {!mapUnmatchedSchedules[mapID][1]}
          </apex:column>
          <apex:column headervalue="Month">
              {!mapUnmatchedSchedules[mapID][2]}
          </apex:column>
          <apex:column headervalue="Year">
              {!mapUnmatchedSchedules[mapID][3]}
          </apex:column>
          <apex:column headervalue="Actualized Amount">
              {!mapUnmatchedSchedules[mapID][4]}
          </apex:column>
      </apex:pageBlocktable>  
          </apex:pageBlock>  
   </apex:form>   
</apex:page>


class

public with sharing class UploadOpportunityScheduleLineItem
{
    
    
    // Global variables
    public string nameFile{get;set;}
    public Blob contentFile{get;set;}
    List<Schedule__c> lstScheduleToUpdate = new List<Schedule__c>();
    public Schedule__C objSchedule{get;set;}
    //String array for taking csv data by line.
    String[] filelines = new String[]{};
    //string array for taking csv columns.
    String[] fileColumns = new String[]{};
    
    //set for storing all id's from csv.
    set<String> opptoupload{get;set;}
    //map for storing data from csv.
    public map<String,String> mapOppIDMonthYearToAccAmount{get;set;}
    public map<String, List<String>> mapUnmatchedSchedules{get;set;}  
    
       
       //Main constructor
    public UploadOpportunityScheduleLineItem()
    {
        //Initalizing required objects.
        objSchedule = new Schedule__c();
        opptoupload = new set<String>();
        mapOppIDMonthYearToAccAmount = new map<String,String>();
        mapUnmatchedSchedules  = new map<String, List<String>>();
    }
    //Method to read file content and check extension and file format.
    public Pagereference ReadFile()
    {
        //If without selecting csv file you clicked on upload it will give error message.
        if(nameFile == null)
        {
            ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.ERROR,'You should select csv file to upload');
            ApexPages.addMessage(errormsg);
            return null;
        }
        //Taking file extension.
        String extension = nameFile.substring(nameFile.lastIndexOf('.')+1);
        //Checking if file extension is .csv.
        if(extension == 'csv' ||extension == 'CSV')
        {
            nameFile=contentFile.toString();
            //Spliting by new line
            filelines = nameFile.split('\n');
            //Spliting values by (,) for checking coloumn size
            fileColumns = filelines[0].split(',');
            //If column size is 5 then only it will proceed.
            if(fileColumns.size() ==5)
            {
                for (Integer i=1;i<filelines.size();i++)
                {
                    String[] inputvalues = new String[]{};
                    inputvalues = filelines[i].split(',');
                       opptoupload.add(inputvalues[0]);
                       
                       mapOppIDMonthYearToAccAmount.put(inputvalues[0]+inputvalues[2]+inputvalues[3],inputvalues[4]);
                    mapUnmatchedSchedules.put(inputvalues[0]+inputvalues[2]+inputvalues[3],inputvalues);
                    lstScheduleToUpdate = new List<Schedule__c>();
                }
                for(Schedule__c objSchedule : [select Opportunity__r.Id ,Month__c,Year__c,
                                                                        Actualized_Amount__c from Schedule__c where
                                                                         Opportunity__c IN :opptoupload])
                {
                    String str = objSchedule.Opportunity__r.Id;
                    String str1;
                    str1 = str.substring(0, 15);
                    if(mapOppIDMonthYearToAccAmount.containsKey(str1 + objSchedule.Month__c +objSchedule.Year__c))
                    {
                        
                        objSchedule.Actualized_Amount__c = decimal.valueOf(mapOppIDMonthYearToAccAmount.get(str1 + objSchedule.Month__c +objSchedule.Year__c).trim());
                        mapUnmatchedSchedules.remove(str1 + objSchedule.Month__c +objSchedule.Year__c);
                        lstScheduleToUpdate.add(objSchedule);
                    }
                }
                //Checking if list is not empty then updating.
                if(lstScheduleToUpdate.Size()>0)
                {
                    update lstScheduleToUpdate;
                }
                ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.info,'File uploaded successfully');
                ApexPages.addMessage(errormsg);
               
                return null;
            }
            else
            {
                ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.ERROR,'There should be 5 columns in csv file');
                ApexPages.addMessage(errormsg);
                return null;
            }
            return null;
        }
        //If file is not csv type then it will give error message.
        else
        {
            ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.ERROR,'File type should be csv type');
            ApexPages.addMessage(errormsg);
            return null;
        }
    }
}
 

Also, please mark this as best answer if it solves your issue...let me know if anything else needed.

All Answers

sandeep sankhlasandeep sankhla
Hi Mahi,

You can use CSV parser for this..plese refer the below example code where I have created one Visualforce page from where I am uplaoding the excel file .csv and extracting the records data and mapping with object columns

please check and let me know if it helps you

page:

<apex:page sidebar="false" controller="UploadOpportunityScheduleLineItem">
    <apex:form >
        <apex:sectionHeader title="Upload data from CSV file"/>
          <apex:pagemessages />  
          <apex:pageBlock >
            <center>
                <apex:inputFile value="{!contentFile}" filename="{!nameFile}" />
                <apex:commandButton action="{!ReadFile}" value="Upload File" id="theButton" style="width:70px;"/>
                <br/> <br/>
            </center>  
      
      <apex:pageBlocktable value="{!mapUnmatchedSchedules}" var="mapID"  title="Unmathed Lines of CSV file">
          <apex:column headervalue="Opportunity Id">
              {!mapUnmatchedSchedules[mapID][0]}
          </apex:column>
          <apex:column headervalue="Oportunnity Name">
              {!mapUnmatchedSchedules[mapID][1]}
          </apex:column>
          <apex:column headervalue="Month">
              {!mapUnmatchedSchedules[mapID][2]}
          </apex:column>
          <apex:column headervalue="Year">
              {!mapUnmatchedSchedules[mapID][3]}
          </apex:column>
          <apex:column headervalue="Actualized Amount">
              {!mapUnmatchedSchedules[mapID][4]}
          </apex:column>
      </apex:pageBlocktable>  
          </apex:pageBlock>  
   </apex:form>   
</apex:page>


class

public with sharing class UploadOpportunityScheduleLineItem
{
    
    
    // Global variables
    public string nameFile{get;set;}
    public Blob contentFile{get;set;}
    List<Schedule__c> lstScheduleToUpdate = new List<Schedule__c>();
    public Schedule__C objSchedule{get;set;}
    //String array for taking csv data by line.
    String[] filelines = new String[]{};
    //string array for taking csv columns.
    String[] fileColumns = new String[]{};
    
    //set for storing all id's from csv.
    set<String> opptoupload{get;set;}
    //map for storing data from csv.
    public map<String,String> mapOppIDMonthYearToAccAmount{get;set;}
    public map<String, List<String>> mapUnmatchedSchedules{get;set;}  
    
       
       //Main constructor
    public UploadOpportunityScheduleLineItem()
    {
        //Initalizing required objects.
        objSchedule = new Schedule__c();
        opptoupload = new set<String>();
        mapOppIDMonthYearToAccAmount = new map<String,String>();
        mapUnmatchedSchedules  = new map<String, List<String>>();
    }
    //Method to read file content and check extension and file format.
    public Pagereference ReadFile()
    {
        //If without selecting csv file you clicked on upload it will give error message.
        if(nameFile == null)
        {
            ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.ERROR,'You should select csv file to upload');
            ApexPages.addMessage(errormsg);
            return null;
        }
        //Taking file extension.
        String extension = nameFile.substring(nameFile.lastIndexOf('.')+1);
        //Checking if file extension is .csv.
        if(extension == 'csv' ||extension == 'CSV')
        {
            nameFile=contentFile.toString();
            //Spliting by new line
            filelines = nameFile.split('\n');
            //Spliting values by (,) for checking coloumn size
            fileColumns = filelines[0].split(',');
            //If column size is 5 then only it will proceed.
            if(fileColumns.size() ==5)
            {
                for (Integer i=1;i<filelines.size();i++)
                {
                    String[] inputvalues = new String[]{};
                    inputvalues = filelines[i].split(',');
                       opptoupload.add(inputvalues[0]);
                       
                       mapOppIDMonthYearToAccAmount.put(inputvalues[0]+inputvalues[2]+inputvalues[3],inputvalues[4]);
                    mapUnmatchedSchedules.put(inputvalues[0]+inputvalues[2]+inputvalues[3],inputvalues);
                    lstScheduleToUpdate = new List<Schedule__c>();
                }
                for(Schedule__c objSchedule : [select Opportunity__r.Id ,Month__c,Year__c,
                                                                        Actualized_Amount__c from Schedule__c where
                                                                         Opportunity__c IN :opptoupload])
                {
                    String str = objSchedule.Opportunity__r.Id;
                    String str1;
                    str1 = str.substring(0, 15);
                    if(mapOppIDMonthYearToAccAmount.containsKey(str1 + objSchedule.Month__c +objSchedule.Year__c))
                    {
                        
                        objSchedule.Actualized_Amount__c = decimal.valueOf(mapOppIDMonthYearToAccAmount.get(str1 + objSchedule.Month__c +objSchedule.Year__c).trim());
                        mapUnmatchedSchedules.remove(str1 + objSchedule.Month__c +objSchedule.Year__c);
                        lstScheduleToUpdate.add(objSchedule);
                    }
                }
                //Checking if list is not empty then updating.
                if(lstScheduleToUpdate.Size()>0)
                {
                    update lstScheduleToUpdate;
                }
                ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.info,'File uploaded successfully');
                ApexPages.addMessage(errormsg);
               
                return null;
            }
            else
            {
                ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.ERROR,'There should be 5 columns in csv file');
                ApexPages.addMessage(errormsg);
                return null;
            }
            return null;
        }
        //If file is not csv type then it will give error message.
        else
        {
            ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.ERROR,'File type should be csv type');
            ApexPages.addMessage(errormsg);
            return null;
        }
    }
}
 

Also, please mark this as best answer if it solves your issue...let me know if anything else needed.
This was selected as the best answer
sandeep sankhlasandeep sankhla
Hi Mahi,

You can also refer this below link

https://iwritecrappycode.wordpress.com/2011/12/01/salesforce-apex-csv-parsing-to-sobject/

please mark this as best answer if it helps you
mahi1mahi1
Hi Sandeep Sankhla,
                              Thanks for your reply, Here i need to upload Ecxel file not a CSV , if the file is CSV it is working fine, but the problem with Excel file i am getting error, Please help




Thanks and Regards....!
sandeep sankhlasandeep sankhla
Hi mahi1,

It is very easy to covert the excel into .csv, you can convert and then use the same code ..

Please check and let me know if this helps you, or you need specific help for excel only..

 
mahi1mahi1
Hi Sandeep,

 Thanks for your immediate reply, That is ok convert the Excel into CSV,
 The user will uplaod Excel or CSV it can allow both format without any interruption this is the  requirement i need, could you please help...



Thanks.....!
 
sandeep sankhlasandeep sankhla
Hi mahi1,

Just put the excel extension also where we are checking csv extension


if(extension == 'csv' ||extension == 'CSV')
        {
here in OR condition you can check for excel extension also..please checkj and let me know if it works or it throws any exception..

 
mahi1mahi1
Hi Sandeep,
                 if(extension == 'xlsx' ||extension == 'XLSX')
                     {
here i tried with xls and xlsx extensions but i am getting following error

Visualforce Error:
System.StringException: BLOB is not a valid UTF-8 string
Error is in expression '{!ReadFile}' in component <apex:commandButton> in page routefinder:uploadtest: Class.RouteFinder.UploadOpportunityScheduleLineItem.ReadFile: line 42, column 1

Thanks and Regards..!

 
sandeep sankhlasandeep sankhla
Hi Mahi,
I have checked and tried many thing till now I didnt find anything related to XLSX parsing in apex..I will let you know if I will get anything related to this..

Till that time you can simply rename and use the csv to parse the data to salesforce..

 
mahi1mahi1
Thanks for your reply Sandeep,

Can we do field mapping while uploading the CSV file to import records into object.

actually my requirement is Import contacts from external file into Contact object Using visualforce page

Thanks and Regards...!
sandeep sankhlasandeep sankhla
Hi mahi,

Yes we can do field mapping..

You can refer the same code which I haev shared above..check adn let me knw if anything needed there