Apex Code Development You need to sign in to do that
Don't have an account?
Victor Nguyen 13 Importing CSV file to multiple objects
Hi everyone,
I'm new to Salesforce and Apex coding. I'm making a function so that user can upload a csv file to generate opportunity and contact. However, it hit the SOQL queries limit, I was able to upload maximum 12 rows.
I need to insert Contact first, in order to get an Contact Id and put it in Opportunity. Since I put the queries inside For loops plus other triggers, it exceeded the queries limit.
I'm planning to create an Apex Batch Process, but not sure if it will solve the problem.
Below is the Apex code and Visualforce page:
Apex:
public class FileUploader {
private ApexPages.StandardController std;
public string nameFile{get;set;}
public Blob contentFile{get;set;}
public Date closeDate {get;set;}
public Id recordType {get;set;}
public Id ownerId {get; set;}
public List<opportunity> opplist{get;set;}
public Contact con {get; set;}
public Opportunity opp {get; set;}
private final String integrationName = 'Inquiry Import';
String campaignlookup;
String[] filelines = new String[]{};
public List<SelectOption> repSelectOptions {get {
List<SelectOption> repSelectOptions = new List<SelectOption> {new SelectOption('','Select One')};
for (User u : [SELECT Id, Name
FROM User
WHERE UserType = 'Standard' AND
IsActive = true
ORDER BY Name asc]) {
repSelectOptions.add(new SelectOption(u.Id, u.Name));
}
return repSelectOptions;
}}
public List<SelectOption> recordOptions {get {
List<SelectOption> recordOptions = new List<SelectOption>{new SelectOption('','Select One')};
for (RecordType r : [SELECT Id, Name FROM RecordType WHERE sobjecttype = 'Opportunity']) {
recordOptions.add(new SelectOption(r.Id, r.Name));
}
return recordOptions;
}}
{
initializeObjects();
}
public void initializeObjects() {
con = new Contact();
opp = new Opportunity();
}
public FileUploader() {
opplist = New List<Opportunity>();
}
/***This function reads the CSV file and inserts records into objects. ***/
public Pagereference ReadFile()
{
opplist.clear();
try{
//Convert the uploaded file which is in BLOB format into a string
nameFile =blobToString( contentFile,'ISO-8859-1');
//Now sepatate every row of the csv file
filelines = nameFile.split('\n');
//Iterate through every line and create a Record for each row
for (Integer i=1; i<filelines.size(); i++)
{
String[] inputvalues = new String[]{};
inputvalues = filelines[i].split(',');
con.FirstName = inputvalues[0] ;
con.LastName = inputvalues[1];
con.MobilePhone = inputvalues[2];
con.Personal_Email__c = inputvalues[3];
con.OwnerId = this.ownerId;
con.Integration__c = this.integrationName;
insert con;
Contact queriedCon = [SELECT Id, FirstName, LastName, AccountId FROM Contact WHERE Id = :con.Id];
opp.CloseDate = this.closeDate;
opp.RecordTypeId = this.recordType;
opp.OwnerId = this.ownerId;
opp.Contact__c = queriedCon.Id;
opp.AccountId = queriedCon.AccountId;
opp.Name = queriedCon.FirstName + ' ' + queriedCon.LastName;
opp.Program_of_Interest__c = inputvalues[4];
campaignlookup = inputvalues[5];
Campaign[] campaignId = [SELECT Id FROM Campaign WHERE Name = :campaignlookup];
if (campaignId.size() > 0) {
opp.CampaignId = campaignId[0].Id;
}
opp.StageName = 'New';
opp.Integration__c = this.integrationName;
insert opp;
opplist.add(opp);
Integration_Origin__c origin = new Integration_Origin__c();
origin.Contact__c = queriedCon.id;
origin.Opportunity__c = opp.id;
origin.Integration__c = [SELECT Id FROM Integration__c WHERE Name = :this.integrationName].Id;
insert origin;
initializeObjects();
campaignlookup = null;
}
}
catch(Exception e){
initializeObjects();
ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.ERROR,'An error has occured reading the CSV file '+e.getMessage());
ApexPages.addMessage(errormsg);
}
return null;
}
public List<Opportunity> getuploadedOpp () {
return opplist;
}
/**
This function converts the input CSV file in BLOB format into a string
@param input Blob data representing correct string in @inCharset encoding
@param inCharset encoding of the Blob data
*/
public static String blobToString(Blob input, String inCharset){
String hex = EncodingUtil.convertToHex(input);
System.assertEquals(0, hex.length() & 1);
final Integer bytesCount = hex.length() >> 1;
String[] bytes = new String[bytesCount];
for(Integer i = 0; i < bytesCount; ++i)
bytes[i] = hex.mid(i << 1, 2);
return EncodingUtil.urlDecode('%' + String.join(bytes, '%'), inCharset);
}
}
Visualforce Page:
<apex:page sidebar="false" controller="FileUploader" docType="html-5.0" lightningStylesheets="true">
<apex:slds />
<apex:form >
<apex:sectionHeader title="Upload data from CSV file"/>
<apex:pagemessages />
<apex:pageBlock >
<apex:pageBlockSection id="RequiredBlock" rendered="true" title="Required Information">
<apex:input required="true" type="date" label="Expected Start Date" value="{!CloseDate}"/>
<apex:selectList required="true" id="recordtypeSelection" size="1" label="Student Type" value="{!recordType}">
<apex:selectOptions value="{!recordOptions}"/>
</apex:selectList>
<apex:selectList required="true" id="userSelect" size="1" label="Advisor" value="{!ownerId}">
<apex:selectOptions value="{!repSelectOptions}"/>
</apex:selectList>
</apex:pageBlockSection>
</apex:pageBlock>
<apex:pageBlock >
<!-- Component to allow user to upload file from local machine -->
<center>
<apex:inputFile value="{!contentFile}" filename="{!nameFile}" /> <apex:commandButton action="{!ReadFile}" value="Upload File" id="theButton" style="width:100px;"/>
<br/> <br/> <font color="red"> <b>Note: Please use the standard template to upload Records. <a href="https://universities.my.salesforce.com/sfc/p/6A000000voho/a/3s000000PTVs/HzZ5Kbu_BdhxG51fDbiHaTMZHguLFRg6gjYC2e75BIg" target="_blank"> Click here </a> to download the template. </b> </font>
</center>
</apex:pageBlock>
<!-- After the user clicks the 'Upload File' button, this section displays the inserted data -->
<apex:pageBlock title="Successfully Imported Students">
<apex:pageblocktable value="{!opplist}" var="opp" rendered="{!NOT(ISNULL(opplist))}">
<apex:column headerValue="Opportunity ID">
<apex:outputField value="{!opp.Id}"/>
</apex:column>
<apex:column headerValue="Opportunity Name">
<apex:outputField value="{!opp.Name}"/>
</apex:column>
<apex:column headerValue="Expected Starte Date">
<apex:outputField value="{!opp.CloseDate}"/>
</apex:column>
<apex:column headerValue="Campaign Source">
<apex:outputField value="{!opp.CampaignId}"/>
</apex:column>
<apex:column headerValue="Opportunity Owner">
<apex:outputField value="{!opp.OwnerId}"/>
</apex:column>
</apex:pageblocktable>
</apex:pageBlock>
</apex:form>
</apex:page>
/* Apex Class for import the multiple objects from single csv file in Visualforc */
You have to use Map to avoid the Soql and DML's inside the for loop.
Please refer the below modified Apex Class:
public class FileUploader {
private ApexPages.StandardController std;
public string nameFile{get;set;}
public Blob contentFile{get;set;}
public Date closeDate {get;set;}
public Id recordType {get;set;}
public Id ownerId {get; set;}
public List<opportunity> opplist{get;set;}
public Contact con {get; set;}
public Opportunity opp {get; set;}
private final String integrationName = 'Inquiry Import';
String campaignlookup;
String[] filelines = new String[]{};
public List<SelectOption> repSelectOptions {get {
List<SelectOption> repSelectOptions = new List<SelectOption> {new SelectOption('','Select One')};
for (User u : [SELECT Id, Name
FROM User
WHERE UserType = 'Standard' AND
IsActive = true
ORDER BY Name asc]) {
repSelectOptions.add(new SelectOption(u.Id, u.Name));
}
return repSelectOptions;
}}
public List<SelectOption> recordOptions {get {
List<SelectOption> recordOptions = new List<SelectOption>{new SelectOption('','Select One')};
for (RecordType r : [SELECT Id, Name FROM RecordType WHERE sobjecttype = 'Opportunity']) {
recordOptions.add(new SelectOption(r.Id, r.Name));
}
return recordOptions;
}}
{
initializeObjects();
}
public void initializeObjects() {
con = new Contact();
opp = new Opportunity();
}
public FileUploader() {
opplist = New List<Opportunity>();
}
/***This function reads the CSV file and inserts records into objects. ***/
public Pagereference ReadFile()
{
Map<Integer,Contact> conMap = new Map<Integer,Contact>();
Map<Integer,Opportunity> oppMap = new Map<Integer,Opportunity>();
Map<Integer,Integration_Origin__c> orginMap = new Map<Integer,Integration_Origin__c>();
Map<String,Campaign> camMap = new Map<String,Campaign>();
Map<Integer,String> camNameMap = new Map<Integer,String>();
set<String> campaignlookupset = new set<String>();
Integration__c integ = [SELECT Id FROM Integration__c WHERE Name = :this.integrationName limit 1];
opplist.clear();
try{
//Convert the uploaded file which is in BLOB format into a string
nameFile =blobToString( contentFile,'ISO-8859-1');
//Now sepatate every row of the csv file
filelines = nameFile.split('\n');
//Iterate through every line and create a Record for each row
for (Integer i=1; i<filelines.size(); i++)
{
String[] inputvalues = new String[]{};
inputvalues = filelines[i].split(',');
con.FirstName = inputvalues[0] ;
con.LastName = inputvalues[1];
con.MobilePhone = inputvalues[2];
con.Personal_Email__c = inputvalues[3];
con.OwnerId = this.ownerId;
con.Integration__c = this.integrationName;
conMap.put(i, con);
//insert con;
//Contact queriedCon = [SELECT Id, FirstName, LastName, AccountId FROM Contact WHERE Id = :con.Id];
opp.CloseDate = this.closeDate;
opp.RecordTypeId = this.recordType;
opp.OwnerId = this.ownerId;
//opp.Contact__c = queriedCon.Id;
//opp.AccountId = queriedCon.AccountId;
opp.Name = inputvalues[0] + ' ' + inputvalues[1];
opp.Program_of_Interest__c = inputvalues[4];
campaignlookup = inputvalues[5];
camNameMap.put(i,campaignlookup);
campaignlookupset.add(campaignlookup);
//Campaign[] campaignId = [SELECT Id FROM Campaign WHERE Name = :campaignlookup];
//if (campaignId.size() > 0) {
// opp.CampaignId = campaignId[0].Id;
// }
opp.StageName = 'New';
opp.Integration__c = this.integrationName;
oppMap.put(i,opp);
//insert opp;
opplist.add(opp);
Integration_Origin__c origin = new Integration_Origin__c();
//origin.Contact__c = queriedCon.id;
//origin.Opportunity__c = opp.id;
origin.Integration__c = integ.Id;
orginMap.put(i,origin);
//insert origin;
initializeObjects();
campaignlookup = null;
}
if(!campaignlookupset.IsEmpty())
{
for(Campaign cam : [SELECT Id,Name FROM Campaign WHERE Name IN: campaignlookupset])
{
camMap.put(cam.Name, cam);
}
}
if(!conMap.IsEmpty()){
insert conMap.Values();
for(Integer i = 1 ; i <= oppMap.Values().size();i++)
{
if(conMap.containsKey(i))
oppMap.get(i).Contact__c = conMap.get(i).Id;
String campName = camNameMap.get(i);
if(camMap.containsKey(campName))
oppMap.get(i).CampaignId = camMap.get(campName).Id;
}
if(!oppMap.IsEmpty())
{
insert oppMap.Values();
for(Integer i = 1 ; i <= orginMap.Values().size();i++)
{
orginMap.get(i).Contact__c = conMap.get(i).Id;
orginMap.get(i).Opportunity__c = oppMap.get(i).Id;
}
if(!orginMap.IsEmpty())
{
insert orginMap.Values();
}
}
}
}
catch(Exception e){
initializeObjects();
ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.ERROR,'An error has occured reading the CSV file '+e.getMessage());
ApexPages.addMessage(errormsg);
}
return null;
}
public List<Opportunity> getuploadedOpp () {
return opplist;
}
/**
This function converts the input CSV file in BLOB format into a string
@param input Blob data representing correct string in @inCharset encoding
@param inCharset encoding of the Blob data
*/
public static String blobToString(Blob input, String inCharset){
String hex = EncodingUtil.convertToHex(input);
System.assertEquals(0, hex.length() & 1);
final Integer bytesCount = hex.length() >> 1;
String[] bytes = new String[bytesCount];
for(Integer i = 0; i < bytesCount; ++i)
bytes[i] = hex.mid(i << 1, 2);
return EncodingUtil.urlDecode('%' + String.join(bytes, '%'), inCharset);
}
}
Thanks,
Maharajan.C
All Answers
/* Apex Class for import the multiple objects from single csv file in Visualforc */
You have to use Map to avoid the Soql and DML's inside the for loop.
Please refer the below modified Apex Class:
public class FileUploader {
private ApexPages.StandardController std;
public string nameFile{get;set;}
public Blob contentFile{get;set;}
public Date closeDate {get;set;}
public Id recordType {get;set;}
public Id ownerId {get; set;}
public List<opportunity> opplist{get;set;}
public Contact con {get; set;}
public Opportunity opp {get; set;}
private final String integrationName = 'Inquiry Import';
String campaignlookup;
String[] filelines = new String[]{};
public List<SelectOption> repSelectOptions {get {
List<SelectOption> repSelectOptions = new List<SelectOption> {new SelectOption('','Select One')};
for (User u : [SELECT Id, Name
FROM User
WHERE UserType = 'Standard' AND
IsActive = true
ORDER BY Name asc]) {
repSelectOptions.add(new SelectOption(u.Id, u.Name));
}
return repSelectOptions;
}}
public List<SelectOption> recordOptions {get {
List<SelectOption> recordOptions = new List<SelectOption>{new SelectOption('','Select One')};
for (RecordType r : [SELECT Id, Name FROM RecordType WHERE sobjecttype = 'Opportunity']) {
recordOptions.add(new SelectOption(r.Id, r.Name));
}
return recordOptions;
}}
{
initializeObjects();
}
public void initializeObjects() {
con = new Contact();
opp = new Opportunity();
}
public FileUploader() {
opplist = New List<Opportunity>();
}
/***This function reads the CSV file and inserts records into objects. ***/
public Pagereference ReadFile()
{
Map<Integer,Contact> conMap = new Map<Integer,Contact>();
Map<Integer,Opportunity> oppMap = new Map<Integer,Opportunity>();
Map<Integer,Integration_Origin__c> orginMap = new Map<Integer,Integration_Origin__c>();
Map<String,Campaign> camMap = new Map<String,Campaign>();
Map<Integer,String> camNameMap = new Map<Integer,String>();
set<String> campaignlookupset = new set<String>();
Integration__c integ = [SELECT Id FROM Integration__c WHERE Name = :this.integrationName limit 1];
opplist.clear();
try{
//Convert the uploaded file which is in BLOB format into a string
nameFile =blobToString( contentFile,'ISO-8859-1');
//Now sepatate every row of the csv file
filelines = nameFile.split('\n');
//Iterate through every line and create a Record for each row
for (Integer i=1; i<filelines.size(); i++)
{
String[] inputvalues = new String[]{};
inputvalues = filelines[i].split(',');
con.FirstName = inputvalues[0] ;
con.LastName = inputvalues[1];
con.MobilePhone = inputvalues[2];
con.Personal_Email__c = inputvalues[3];
con.OwnerId = this.ownerId;
con.Integration__c = this.integrationName;
conMap.put(i, con);
//insert con;
//Contact queriedCon = [SELECT Id, FirstName, LastName, AccountId FROM Contact WHERE Id = :con.Id];
opp.CloseDate = this.closeDate;
opp.RecordTypeId = this.recordType;
opp.OwnerId = this.ownerId;
//opp.Contact__c = queriedCon.Id;
//opp.AccountId = queriedCon.AccountId;
opp.Name = inputvalues[0] + ' ' + inputvalues[1];
opp.Program_of_Interest__c = inputvalues[4];
campaignlookup = inputvalues[5];
camNameMap.put(i,campaignlookup);
campaignlookupset.add(campaignlookup);
//Campaign[] campaignId = [SELECT Id FROM Campaign WHERE Name = :campaignlookup];
//if (campaignId.size() > 0) {
// opp.CampaignId = campaignId[0].Id;
// }
opp.StageName = 'New';
opp.Integration__c = this.integrationName;
oppMap.put(i,opp);
//insert opp;
opplist.add(opp);
Integration_Origin__c origin = new Integration_Origin__c();
//origin.Contact__c = queriedCon.id;
//origin.Opportunity__c = opp.id;
origin.Integration__c = integ.Id;
orginMap.put(i,origin);
//insert origin;
initializeObjects();
campaignlookup = null;
}
if(!campaignlookupset.IsEmpty())
{
for(Campaign cam : [SELECT Id,Name FROM Campaign WHERE Name IN: campaignlookupset])
{
camMap.put(cam.Name, cam);
}
}
if(!conMap.IsEmpty()){
insert conMap.Values();
for(Integer i = 1 ; i <= oppMap.Values().size();i++)
{
if(conMap.containsKey(i))
oppMap.get(i).Contact__c = conMap.get(i).Id;
String campName = camNameMap.get(i);
if(camMap.containsKey(campName))
oppMap.get(i).CampaignId = camMap.get(campName).Id;
}
if(!oppMap.IsEmpty())
{
insert oppMap.Values();
for(Integer i = 1 ; i <= orginMap.Values().size();i++)
{
orginMap.get(i).Contact__c = conMap.get(i).Id;
orginMap.get(i).Opportunity__c = oppMap.get(i).Id;
}
if(!orginMap.IsEmpty())
{
insert orginMap.Values();
}
}
}
}
catch(Exception e){
initializeObjects();
ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.ERROR,'An error has occured reading the CSV file '+e.getMessage());
ApexPages.addMessage(errormsg);
}
return null;
}
public List<Opportunity> getuploadedOpp () {
return opplist;
}
/**
This function converts the input CSV file in BLOB format into a string
@param input Blob data representing correct string in @inCharset encoding
@param inCharset encoding of the Blob data
*/
public static String blobToString(Blob input, String inCharset){
String hex = EncodingUtil.convertToHex(input);
System.assertEquals(0, hex.length() & 1);
final Integer bytesCount = hex.length() >> 1;
String[] bytes = new String[bytesCount];
for(Integer i = 0; i < bytesCount; ++i)
bytes[i] = hex.mid(i << 1, 2);
return EncodingUtil.urlDecode('%' + String.join(bytes, '%'), inCharset);
}
}
Thanks,
Maharajan.C