Dtyjlui

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;



} 

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.

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.

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);

 }