1

I have a csv file with data in each row that gets upserted into both the standard "Contact" object and a custom object called "Certification" by looping over the rows in the csv file. As part of the upload, I validate fields in each row and if there is an error I add the offending row to an error List. If the row passes validation, I add it to a separate List. There are 3 totals rows in my CSV file and 1 by design fails validation.

After separating the good rows from the bad when I go to insert data into the related object there are less rows to insert than rows in the original csv file so I get a List Index out of Bounds issue. The error happens when I try to connect the the related Certification record to the Contact record.

 public String nameFile{get;set;}
 public Blob contentFile{get;set;}
 String[] filerows = new String[]{};

 List<Contact> conRecords= new List<Contact>();
 List<Certification__c> certRecords = new List<Certification__c>();
 List<Row_Error_Log__c> rowErrors = new List<Row_Error_Log__c>();
 List<Row_Error_Log__c> allRowErrors = new List<Row_Error_Log__c>();

 public PageReference ReadCSVFile()    {

   //Convert the uploaded file which is in BLOB format into a string
   nameFile = blobToString( contentFile,'ISO-8859-1');

  //Now separate every row of the csv file
  filerows = nameFile.split('\n');

  for (Integer i=1;i<filerows.size();i++)  {
       String[] inputValues = new String[]{};
       inputValues = filerows[i].split(',');

       Contact newContact = new Contact();
       newContact.AccountId =   inputValues[0];
       newContact.FirstName = inputValues[1];
       newContact.LastName = inputValues[2];
       newContact.Email= inputValues[3];
       newContact.Date_of_Birth__c = Date.valueOf(inputValues[4]);

       rowErrors = validateContact( newContact );

       if( rowErrors.isEmpty()) {
           conRecords.add(newContact);
       }
       else {
           allRowErrors.addAll(rowErrors);
       }
   }
   upsert conRecords Contact_Email__c;


     List<Certification__c> certificationList= new List<Certification__c>();

     for(Integer j=1;j<filerows.size();j++) {
       String[] columnValues = new String[]{};
       columnValues = filerows[j].split(',');

       Certification__c newCert= new Certification__c ();
       newCert.Title= columnValues[5];         
       newCert.Contact__c = conRecords.get(j-1).Id; // problem is here

       certRecords.add(newCert);
     }

     insert certRecords;

} 
  • If the conRecords never had values in there, then conRecords.get(j-1).Id will always fail. Are you sure that conRecords has values in it? Have you tried verifying the size of the list? – Jayant Das Feb 1 at 18:39
  • Yes @jayant-das, it has 2 valid rows by design and 1 invalid row. – user1669296 Feb 1 at 18:53
  • Can you also post the exact error what you are getting? If the list has records, then the line where you have mentioned //problem is here shouldn't occur. – Jayant Das Feb 1 at 18:55
  • "List out of bounds 2" was the error. The error doesn't occur if there are not invalid rows. The error occurs because there are 3 total rows in the filerows variable and in the second loop I was looping over the same variable and trying to set the contact ID using conRecords.get(j-1).Id, since there are only 2 rows in conRecords (because 1 row was invalid) you get an error. – user1669296 Feb 1 at 19:13
2

You could go with something like an extra list:

 String[] validFilerows = new String[]{};
 ...
   if( rowErrors.isEmpty()) {
       validFilerows.add(filerows[i]);
       conRecords.add(newContact);
   }
 ...

and change your second loop to use validFilerows which is guaranteed to have the corresponding data.

  • Wouldn't this still lead to the problem say conRecords never got populated if the condition was not met? Based on OP's description, the error is being thrown at this line conRecords.get(j-1).Id? – Jayant Das Feb 1 at 19:03
  • I think just checking validFilerows has size would eliminate that @jayant-das? – user1669296 Feb 1 at 19:08
  • @user1669296 may be I am missing something here. But my point is what if rowErrors.isEmpty() returned false, in that case both array/list will be empty. Now if you were to check the array size, you could do that in your current code just by checking conRecords size as well. Don't access the list if it's empty. – Jayant Das Feb 1 at 19:11
  • I was able to follow the issue now. @user1669296, I added one more option, I think that would work too. – Jayant Das Feb 1 at 19:21
1

There's plenty of logical ways to solve this. You just need to make sure that you account for the skipped rows as you traverse the smaller list.

One way would be to do something like this:

Set<Integer> skippedRowNumbers = new Set<Integer>();

// ...
if (rowErrors.isEmpty()) {
    conRecords.add(newContact);
} else {
    allRowErrors.addAll(rowErrors);
    skippedRowNumbers.add(i);
}

Then, in your next loop,

Integer offset = 0;
for(Integer j=1;j<filerows.size();j++) {
    if (skippedRowNumbers.contains(j)) {
        offset++;
        continue;
    }
    // ...
   newCert.Contact__c = conRecords.get(j - offset - 1).Id;
}

Essentially, you're recalculating the index in conRecords based on how many errored rows you've traversed so far.

There are other ways to do this too; you could use a Map<Integer, Integer> to map from file rows to Contact rows, or take other approaches. It's just a logic problem.

0

I would think this should work too. Instead of iterating over fileRows, just iterate over the conRecords list.

The list will always consist of the records those were successfully upserted and ensure that you never run out of bounds while accessing values from the list of records those were upserted.

for(Integer j = 0; j < conRecords.size(); j++) {  // changes here - used list

   String[] columnValues = new String[]{};
   columnValues = filerows[j+1].split(',');  // changes here - index
   Certification__c newCert= new Certification__c ();
   newCert.Title= columnValues[5];         
   newCert.Contact__c = conRecords.get(j).Id;  // changes here - index

   certRecords.add(newCert);
 }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.