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
Churchill19Churchill19 

CSV Parser - how to link a existing record from a related object when inserting?

Hi All,

 

wonder if anyone can help me out with a problem that i have when inserting a .CSV through Visualforce to opportunity... i have a Lookup field  : -

 

o.BrokerSalesAgent__c = inputvalues[4];

which need to link an custom object which contains existing records that i want to link to via the name of the record. I have managed to link this through by using the record ID on the .csv but i have a 3rd party that will not have access to record ID's.

 

when try to use the name of the record i get the following error message: - 

 

Visualforce Error

 


System.StringException: Invalid id: Sean Broker Team

Error is in expression '{!ReadFile}' in component <apex:commandButton> in page uploadop

 

 

Class.FileUploadOpp.ReadFile: line 33, column 1

 

I know that i have to write some code to search for the existing records (ID,Name) but not sure how to approach this?

 

the code below shows the inputvalues from my .csv file mapping them to the fields in the opportunity object. 

 

 

 

rowCount = 0;
        filelines = nameFile.split('\n');
        opptoupload = new List<Opportunity>();
        
        for (Integer i=1;i<filelines.size();i++)
        {
            String[] inputvalues = new String[]{};
            inputvalues = filelines[i].split(',');
            rowCount = filelines.size() -1;
            Opportunity o = new Opportunity();
            o.RecordTypeId = inputvalues[0];
            o.Broker_Ref__c = inputvalues[1];
            o.Name = inputvalues[2];
            o.StageName = inputvalues[3];
            o.BrokerSalesAgent__c = inputvalues[4];
            o.AccountId = inputvalues[5];
            o.CloseDate = date.valueof(inputvalues[6]);

             opptoupload.add(o);
        }

 Can anyone point me in the right direction or help me with a example of how i can do this please?

 

thanks,

 

sean.

SaraagSaraag

When I had to do it, I looped through csv file lines, added record name to a set. using the set, did a SOQL to build a map <Id,Name>,   then finally built records using namemap.get(inputvalue[4]). I oversimplified, but you get the point.

 

 

Saraag

 

Churchill19Churchill19

Hi Saraag,

 

do you have an example that you can send me?

 

thanks,

 

sean.

SaraagSaraag
public with sharing class SS_CloseCases_Controller {
 
   public  Transient Boolean              success       {get;set;}
   Public  Transient Blob                 fileContent   {get;set;}
   private Transient Blob                 Attachment;
   Public  Transient String               fileName      {get;set;}
   Public  Transient String               fileType      {get;set;}
   Public  Transient Integer              fileSize      {get;set;}
   Public  Transient String               fileString    {get;set;}
   Public  Integer                        fileLinesSize {get;set;}   
   private Generic_CSVReader              csvReader;
   private Transient List<string>         fileLines;
   private final static Integer           MAXFILESIZE=2097152; // 2MB
   private final static Integer           MAXROWS=1000;
   private final static set<String> allowedFileMIMEs =new set<String>{'text/comma-separated-values',
                                                            'text/csv',  
                                                            'application/csv', 
                                                            'application/excel', 
                                                            'application/vnd.ms-excel', 
                                                            'application/vnd.msexcelapplication/vnd.ms-excel',
                                                            'application/octet-stream'
                                                            };

//Close cases along with Internalcase number. Format: Casenumber,reviewnotes,internalcasenumber public pageReference closeInternalCase(){ success=true; if(fileContent==NULL){ //clicked close without attaching a file ApexPages.Message myMsg = new ApexPages.Message(ApexPages.severity.ERROR,'Please select a file.'); ApexPages.addMessage(myMsg); return NULL; } if(!fileName.contains('.csv')){ //Not a csv file ApexPages.Message myMsg = new ApexPages.Message(ApexPages.severity.ERROR,'Not a CSV File'); fileContent=NULL; ApexPages.addMessage(myMsg); return NULL; } if(!allowedFileMIMEs.contains(fileType)){ //Not a valid MIME ApexPages.Message myMsg = new ApexPages.Message(ApexPages.severity.ERROR,'Invalid File Contents'); fileContent=NULL; ApexPages.addMessage(myMsg); return NULL; } else{ if(fileContent.size()>MAXFILESIZE){ //currently limited to 2MB ApexPages.Message fileMsg = new ApexPages.Message(ApexPages.severity.ERROR,'File size should be less than 2MB.'); ApexPages.addMessage(fileMsg); fileContent=NULL; return NULL; } else{ csvReader=new Generic_CSVReader(); List<string> fileLines=csvReader.readCVSList(fileContent); fileLinesSize=fileLines.size(); if(fileLinesSize>MAXROWS){ //Limited to 1000 rows ApexPages.Message fileMsg = new ApexPages.Message(ApexPages.severity.ERROR,'Sorry, lite version is limited to 1000 cases.'); ApexPages.addMessage(fileMsg); fileContent=NULL; return NULL; } else{ //Processing... Transient List<case> caseupdateList=new List<case>(); Transient Map<string,List<String>> casenamemap=new Map<string,List<String>>(); Transient Map<string,id> internalcasemap=new Map<string,id>(); Transient List<String> innercasenameList=new List<String>(); Transient List<case> innercaseList; Transient List<string> casenumberList=new List<string>(); Transient List<case> caseList; for(integer i=1;i<fileLinesSize&i<1000;i++){ String[] inputvalues = filelines[i].split(','); string s=inputvalues[0]; if(s.length()!=8){ //casenumber is missing characters ApexPages.Message fileMsg = new ApexPages.Message(ApexPages.severity.ERROR,'Please review input data,one or more rows under casenumber are missing leading zeros.'); ApexPages.addMessage(fileMsg); fileContent=NULL; return NULL; } casenumberList.add(s); casenameMap.put(s,inputvalues); string inc=inputvalues[2].normalizespace(); //remove spaces if(inc.length()!=8){ //Internalcase number is not correct ApexPages.Message fileMsg = new ApexPages.Message(ApexPages.severity.ERROR,'Please review input data,one or more rows under internalcase column are missing leading zeros.'+inc+'size:'+inc.length()); ApexPages.addMessage(fileMsg); fileContent=NULL; return NULL; } innercasenameList.add(inc); } system.debug(logginglevel.FINEST,'InternalcaseNAMElIST:'+innercasenameList); caseList=[select casenumber,status,review_notes__c,reason from case where casenumber in:casenumberList]; innercaseList=[select id,casenumber from case where casenumber in:innercasenameList]; for(case c:innercaselist){ internalcasemap.put(c.casenumber,c.id); } system.debug(logginglevel.error,'InternalcaseMap:'+internalcasemap); for(case c:caseList){ c.status='Closed'; c.Reason='Duplicate'; c.Review_Notes__c=casenameMap.get(c.casenumber)[1]+'\n'+c.Review_Notes__c; c.Related_Internal_CRD_Case__c=internalcasemap.get(casenameMap.get(c.casenumber)[2].normalizespace()); caseupdateList.add(c); } system.debug(logginglevel.error,'caseupdateList:'+caseupdateList); try{ update caseupdateList; }catch(exception e){ success=false; system.debug(logginglevel.error,'Exception:'+e); } } } } confirmationMessage(); return NULL; }//end closeInternalCase() private void confirmationMessage(){ if(success){ Id jobid=JobLogger.create(NULL,NULL,NULL,'Closed cases',UserInfo.getUserId(),NULL,True,'CloseCases',fileContent); ApexPages.Message fileMsg = new ApexPages.Message(ApexPages.severity.CONFIRM,'Successfully closed '+fileLinesSize+' cases.'+' For your reference, please note the Job ID:'+jobid); ApexPages.addMessage(fileMsg); fileContent=NULL; }else{ fileContent=NULL; ApexPages.Message fileMsg = new ApexPages.Message(ApexPages.severity.FATAL,'System encountered error updating cases. Please try again.'); ApexPages.addMessage(fileMsg); } } public class closecasesException extends Exception{} }

Code was working the last time I checked. I had a csv file with casenumber,notes,relatedcasenumber as input. I had to find the Ids for casenumber and internalcasenumber and then close them. Please excuse any errors.

 

Saraag