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
Sales person 375Sales person 375 

import unique accounts from csv file using Visualforce page

Im new to salesforce, im working on visualforce page to import Account and contacts to salesforce.

here is my requiremnt, please guide me to achive this. 
 
Give the customer ability to import Accounts from external csv file to salesforce with button on custom Visualforce page.
Read the csv file and display the content in the Tabular form in the same Visualforce page.
Verify the Duplicate records based on Phone number, if the phone number of the import record matches with existing Account record then verify for Email field match, if matches then populate error message as Duplicate record on Visualforce page Table under Status column for particular record. Otherwise insert record and update the status column on Visualforce page as Success.
Extend the functionality to multiple Objects (Account, Contact, and Opportunity) from single csv file.

here is my code: im getting error "common.apex.runtime.impl.ExecutionException: List index out of bounds: 5" and status is not getting updated. 

Please help me

VF:
<apex:page controller="importDataFromCSVController">
    <apex:form >
        <apex:pagemessages />
        <apex:pageBlock >
            <apex:pageBlockSection columns="4">
                  <apex:inputFile value="{!csvFileBody}"  filename="{!csvAsString}"/>
                  <apex:commandButton value="Import Account" action="{!importCSVFile}"/>
            </apex:pageBlockSection>
        </apex:pageBlock>
        <apex:pageBlock >
           <apex:pageblocktable value="{!accList}" var="acc">
              <apex:column value="{!acc.name}" />
              <apex:column value="{!acc.AccountNumber}" />
              <apex:column value="{!acc.Type}" />
              <apex:column value="{!acc.Accountsource}" />
              <apex:column value="{!acc.Industry }" />
               <apex:column headerValue="Status" >
              <apex:outputText id="status"/>
              </apex:column>
           </apex:pageblocktable>
        </apex:pageBlock>
   </apex:form>
</apex:page>

Apex Class:

public class importDataFromCSVController {
public Blob csvFileBody{get;set;}
public string csvAsString{get;set;}
public string csvStatus{get;set;}
public String[] csvFileLines{get;set;}
public List<account> acclist{get;set;}
public string[] csvRecordData;
public importDataFromCSVController(){
    csvFileLines = new String[]{};
    acclist = New List<Account>();
  }
  
  public void importCSVFile()
  {
       try{
           csvAsString = csvFileBody.toString();
           csvFileLines = csvAsString.split('\n');
            
           for(Integer i=1;i<csvFileLines.size();i++)
           {
               Account accObj = new Account() ;
               csvRecordData = csvFileLines[i].split(',');
               accObj.name = csvRecordData[0] ;            
               accObj.accountnumber = csvRecordData[1];
               accObj.Type = csvRecordData[2];
               accObj.AccountSource = csvRecordData[3];  
               accObj.Industry = csvRecordData[4];
               uploadStatus(accObj);
               csvRecordData[5] = csvStatus;
            }
        insert acclist;
        }
        catch (Exception e)
        {
            ApexPages.Message errorMessage = new ApexPages.Message(ApexPages.severity.ERROR,'error here');
            ApexPages.addMessage(errorMessage);
        } 
  }
  
public List<Account> uploadStatus(Account a)
   {
       for(Account acc:[SELECT Name, Accountnumber FROM Account])
       {
           if(acc.name == a.name && acc.accountnumber == a.accountnumber)
           {
               this.csvStatus = 'Duplicate';
                system.debug('inside if');
           }
           else
           {
               system.debug('inside else');
               acclist.add(a);
               this.csvStatus = 'unique';
               
           }
       }
       return acclist;
   }
}
 
Best Answer chosen by Sales person 375
salesforceMannsalesforceMann
Hi sales person.

I managed to spend a few minutes and put the code for you. Its working well (although it may not be idiot proof).
Now, you can obviously use the code below, but more importantly go ahead and ask me if you have any doubts on why we did something this way and not another.

Controller:
public class importDataFromCSVController_mann {
    
    public Blob csvFileBody{get;set;}
    public string csvAsString{get;set;}
    public string csvStatus{get;set;}
    public String[] csvFileLines{get;set;}
    public string[] csvRecordData;
    public List<account> acc_lst;
    public Map<String,String> acc_map;
    
    
    
    //define a wrapper class
    public class wrapAccount {
        public Account acc {get;set;}
        public String csvStatus {get;set;}
        
        public wrapAccount(Account a, Map<String,String> acc_map) {
            acc = a;
            if(acc_map.containsKey(a.Name)) {
                csvStatus = 'Duplicate';
            } else {
                csvStatus = 'Unique';
                //write you insert statement here (am commenting it to keep my DB clean :))
                //insert a;
                
            }
            
        }
    }
    
    //wrapperobject to display on VF
    public List<wrapAccount> wrapAccount_lst {get;set;}
    
    
    public List<wrapAccount> getAccounts() {
        return wrapAccount_lst;
    }
    
    //populate wrapperObject
    public pagereference importCSVFile() {
        csvAsString = csvFileBody.toString();
        csvFileLines = csvAsString.split('\n');
        system.debug('csvFileLines= '+csvFileLines+' csvFileLines.size() = '+csvFileLines.size());
        
        //get all accounts
        acc_lst = new List<account>([select Name, Accountnumber from account]);
        //initialize the map
        acc_map = new Map<String,String>();
        
        for (account a: acc_lst) {
            system.debug('a = '+a);
            acc_map.put(a.Name,a.AccountNumber);
        }
          
        wrapAccount_lst = new List<wrapAccount>();
        for(Integer i=0;i<csvFileLines.size();i++) { //assuming no header in the CSV
            
            Account a = new Account();
			csvRecordData = csvFileLines[i].split(',');
            a.name = csvRecordData[0] ;            
            a.accountnumber = csvRecordData[1];
            a.Type = csvRecordData[2];
            a.AccountSource = csvRecordData[3];  
            a.Industry = csvRecordData[4];
            
            
            wrapAccount_lst.add(new wrapAccount(a,acc_map));
             
        }
        return null;
    }
 

}

VF page:
<apex:page controller="importDataFromCSVController_mann">
    <apex:form >
        <apex:pagemessages />
        <apex:pageBlock >
            <apex:pageBlockSection columns="4">
                  <apex:inputFile value="{!csvFileBody}"  filename="{!csvAsString}"/>
                  <apex:commandButton value="Import Account" action="{!importCSVFile}"/>
            </apex:pageBlockSection>
        </apex:pageBlock>
        <apex:pageBlock >
           <apex:pageblocktable value="{!Accounts}" var="acc">
              <apex:column value="{!acc.acc.name}" />
              <apex:column value="{!acc.acc.AccountNumber}" />
              <apex:column value="{!acc.acc.Type}" />
              <apex:column value="{!acc.acc.Accountsource}" />
              <apex:column value="{!acc.acc.Industry }" />
               <apex:column headerValue="Status" >
              <apex:outputText id="status" value="{!acc.csvStatus}"/>
              </apex:column>
           </apex:pageblocktable>
        </apex:pageBlock>
   </apex:form>
</apex:page>

Please mark as best answer if this is good.
Thank you.
Mann.


 

All Answers

salesforceMannsalesforceMann
Can you paste the entire debug log? I think I know the issue but need more information to explain.
Sales person 375Sales person 375
I hvae copied debug log to goodgle doc. Please refer from below link. Thank you

https://docs.google.com/document/d/1xZBCe9tDMSZhLp8A49Nv3msVtNZ3aIUPV2UdX926rTw/edit?usp=sharing
salesforceMannsalesforceMann
Hi Sales person,
The error is with the way you are handling csvStatus field. 
I am writing a corrected code now. Hopefully will complete before sleeping :P
Sales person 375Sales person 375
Thank you. I think i should use "rerender" to refresh the upadted status value in visualforce page.
salesforceMannsalesforceMann
Not quite completely. Actually, you need a wrapper class **Eureka** ! :)
Sales person 375Sales person 375
Yes thought so, but Im not much familier with wrapper class in saleforce.

Thank you. :D you are awesome.
salesforceMannsalesforceMann
Yes, we are on the same page. i.e. account : oracle site is 1:1 relationship.

as far as contacts are concerned  we took a business call to decide where a contact should reside. there were of course cases where it wasn't easy but nevertheless in favor of simplicity we accepted this as a risk.

besides we had a process in place for internal data audit that would attempt to keep the data clean.
salesforceMannsalesforceMann
Hi Sales Person, 

I appologize for the delay. I will try to give you a working code by EOD today UTC+8hrs. Is that okay with you?

Regards
Mann
salesforceMannsalesforceMann
Hi sales person.

I managed to spend a few minutes and put the code for you. Its working well (although it may not be idiot proof).
Now, you can obviously use the code below, but more importantly go ahead and ask me if you have any doubts on why we did something this way and not another.

Controller:
public class importDataFromCSVController_mann {
    
    public Blob csvFileBody{get;set;}
    public string csvAsString{get;set;}
    public string csvStatus{get;set;}
    public String[] csvFileLines{get;set;}
    public string[] csvRecordData;
    public List<account> acc_lst;
    public Map<String,String> acc_map;
    
    
    
    //define a wrapper class
    public class wrapAccount {
        public Account acc {get;set;}
        public String csvStatus {get;set;}
        
        public wrapAccount(Account a, Map<String,String> acc_map) {
            acc = a;
            if(acc_map.containsKey(a.Name)) {
                csvStatus = 'Duplicate';
            } else {
                csvStatus = 'Unique';
                //write you insert statement here (am commenting it to keep my DB clean :))
                //insert a;
                
            }
            
        }
    }
    
    //wrapperobject to display on VF
    public List<wrapAccount> wrapAccount_lst {get;set;}
    
    
    public List<wrapAccount> getAccounts() {
        return wrapAccount_lst;
    }
    
    //populate wrapperObject
    public pagereference importCSVFile() {
        csvAsString = csvFileBody.toString();
        csvFileLines = csvAsString.split('\n');
        system.debug('csvFileLines= '+csvFileLines+' csvFileLines.size() = '+csvFileLines.size());
        
        //get all accounts
        acc_lst = new List<account>([select Name, Accountnumber from account]);
        //initialize the map
        acc_map = new Map<String,String>();
        
        for (account a: acc_lst) {
            system.debug('a = '+a);
            acc_map.put(a.Name,a.AccountNumber);
        }
          
        wrapAccount_lst = new List<wrapAccount>();
        for(Integer i=0;i<csvFileLines.size();i++) { //assuming no header in the CSV
            
            Account a = new Account();
			csvRecordData = csvFileLines[i].split(',');
            a.name = csvRecordData[0] ;            
            a.accountnumber = csvRecordData[1];
            a.Type = csvRecordData[2];
            a.AccountSource = csvRecordData[3];  
            a.Industry = csvRecordData[4];
            
            
            wrapAccount_lst.add(new wrapAccount(a,acc_map));
             
        }
        return null;
    }
 

}

VF page:
<apex:page controller="importDataFromCSVController_mann">
    <apex:form >
        <apex:pagemessages />
        <apex:pageBlock >
            <apex:pageBlockSection columns="4">
                  <apex:inputFile value="{!csvFileBody}"  filename="{!csvAsString}"/>
                  <apex:commandButton value="Import Account" action="{!importCSVFile}"/>
            </apex:pageBlockSection>
        </apex:pageBlock>
        <apex:pageBlock >
           <apex:pageblocktable value="{!Accounts}" var="acc">
              <apex:column value="{!acc.acc.name}" />
              <apex:column value="{!acc.acc.AccountNumber}" />
              <apex:column value="{!acc.acc.Type}" />
              <apex:column value="{!acc.acc.Accountsource}" />
              <apex:column value="{!acc.acc.Industry }" />
               <apex:column headerValue="Status" >
              <apex:outputText id="status" value="{!acc.csvStatus}"/>
              </apex:column>
           </apex:pageblocktable>
        </apex:pageBlock>
   </apex:form>
</apex:page>

Please mark as best answer if this is good.
Thank you.
Mann.


 
This was selected as the best answer
Sales person 375Sales person 375
Hi Mann,

its perfect. Thank you. 

i want to know that only solution is to use wrapper class or we can use any other method to this senario. As per my understanding we should use wrapper class to populate date back on VF, just curious to know if there is any other approach to this.

Thankyou.
salesforceMannsalesforceMann
Hi sales person, to the best of my knowledge you need a wrapper class for such scenarios.