+ Start a Discussion
PanchoPancho 

Need help dealing with custom CSV import with related objects

Hi everyone,

Any help with this problem would be greatly appreciated.  I am currently trying to do a custom CSV import in order to automate the process of importing a CSV every day.  I have a VisualForce page that accepts the filename of the CSV file and calls my apex class to do the upload. (both are below) The CSV parser is working fine, after testing.  The problem I am having is with grabbing the related object ids for the import.  The error I am getting is "System.ListException: List index out of bounds: 17

Error is in expression '{!ReadFile}' in page fotaupload Class.FOTAuploader.ReadFile: line 116, column 1"

 

I think my problem is around the section with the comment "//create a list of the related zip code ids"

 

About my objects:

I have a zip code object that is a related list to my Activations object.

 

 

***********************************************************

FOTAuploader.cls

 

 

 

public with sharing class FOTAuploader {

 

    public string nameFile{get;set;}

    public Blob contentFile{get;set;}

    public Integer rowCount{get;set;}

    public Integer colCount{get;set;}

    List<Activations__c> actvstoupload;

    public Zip_Code_Master__c tempZip;

   

 

// taken from stackoverflow.com/questions/10425925/how-to-parse-a-csv-in-salesforce

    public static List<List<String>> parseCSV(String contents,Boolean skipHeaders) {

List<List<String>> allFields = new List<List<String>>();

 

// replace instances where a double quote begins a field containing a comma

// in this case you get a double quote followed by a doubled double quote

// do this for beginning and end of a field

contents = contents.replaceAll(',"""',',"DBLQT').replaceall('""",','DBLQT",');

// now replace all remaining double quotes - we do this so that we can reconstruct

// fields with commas inside assuming they begin and end with a double quote

contents = contents.replaceAll('""','DBLQT');

// we are not attempting to handle fields with a newline inside of them

// so, split on newline to get the spreadsheet rows

List<String> lines = new List<String>();

try {

lines = contents.split('\n');

} catch (System.ListException e) {

System.debug('Limits exceeded?' + e.getMessage());

}

Integer num = 0;

for(String line : lines) {

// check for blank CSV lines (only commas)

if (line.replaceAll(',','').trim().length() == 0) break;

 

List<String> fields = line.split(',');        

List<String> cleanFields = new List<String>();

String compositeField;

Boolean makeCompositeField = false;

for(String field : fields) {

if (field.startsWith('"') && field.endsWith('"')) {

cleanFields.add(field.replaceAll('DBLQT','"'));

} else if (field.startsWith('"')) {

makeCompositeField = true;

compositeField = field;

} else if (field.endsWith('"')) {

compositeField += ',' + field;

cleanFields.add(compositeField.replaceAll('DBLQT','"'));

makeCompositeField = false;

} else if (makeCompositeField) {

compositeField +=  ',' + field;

} else {

cleanFields.add(field.replaceAll('DBLQT','"'));

}

}

 

allFields.add(cleanFields);

}

if (skipHeaders) allFields.remove(0);

return allFields;                

}

 

 

   

    public Pagereference ReadFile()

    {                  

            //create a restore point incase the upload fails it can back out everything.

            Savepoint sp = Database.setSavepoint();

            

        actvstoupload = new List<Activations__c>();      

        List<List<String>> parsedCSV = new List<List<String>>();

        List<String> zips = new List<String>();

       

        //fill up the parsedCSV table

        rowCount = 0;

        colCount = 0;

        if (contentFile != null){

            String fileString = contentFile.toString();

            parsedCSV = parseCSV(fileString, false);

            rowCount = parsedCSV.size();

            for (List<String> row : parsedCSV){

                if (row.size() > colCount){

                    colCount = row.size();

                }

            }

         }

        

      //create a list of the related zip code ids

 

        for (Integer i=1;i<parsedCSV.size();i++)

        {

        zips[i] = parsedCSV[i][6].replaceAll('\"','');

        }

        List<Zip_Code_Master__c> zipList= [select id from Zip_Code_Master__c where name =:zips];

       

       

       

        for (Integer i=1;i<parsedCSV.size();i++)

        {

           

            Activations__c a = new Activations__c();

           

            a.Zip_Code_of_Activation__c = zipList[i].id;

           

           //process quantity field

            a.ActQty__c = Double.valueOf(parsedCSV[i][18].replaceAll('\"',''));    

 

                

                     //process date field  -- filter out the hour and minutes from the Date field.

                     Date dT = date.parse(parsedCSV[i][0].replaceAll('\"','').trim().substringBefore(' '));

            a.Date_entry__c = dT;  //get date from visualforce page 

 

 

            actvstoupload.add(a);

        }

        try{

        insert actvstoupload;

        }

        catch (Exception e)

        {

            Database.rollback(sp);

            ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.ERROR,'An error has occured. Please check the template or try again later');

            ApexPages.addMessage(errormsg);

        }   

        return null;

    }

   

    public List<Activations__c> getuploadedActivations()

    {

        if (actvstoupload!= NULL)

            if (actvstoupload.size() > 0)

                return actvstoupload;

            else

                return null;                   

        else

            return null;

    }           

}

 

**********************************************************************************

 

FOTAupload.page

 

<apex:page sidebar="false" controller="FOTAuploader">
<apex:form >
<apex:sectionHeader title="Upload FOTA data from CSV file"/>
<apex:pagemessages />
<apex:pageBlock >
<center>
<apex:inputFile value="{!contentFile}" filename="{!nameFile}" /> <apex:commandButton action="{!ReadFile}" value="Upload File" id="theButton" style="width:70px;"/>
<br/> <br/> <font color="red"> <b>Note: Please use the standard .CSV file from the Retail Activations Portal. </b> </font>
</center>


<apex:pageblocktable value="{!uploadedActivations}" var="actv" rendered="{!NOT(ISNULL(uploadedActivations))}">
<apex:column headerValue="Actv Date">
<apex:outputField value="{!actv.Date_entry__c}"/>
</apex:column>
<apex:column headerValue="Zip">
<apex:outputField value="{!actv.Zip_Code_of_Activation__c}"/>
</apex:column>
<apex:column headerValue="Qty">
<apex:outputField value="{!actv.ActQty__c}"/>
</apex:column>
<apex:column headerValue="City-State">
<apex:outputField value="{!actv.City_State_Concatenation__c}"/>
</apex:column>
</apex:pageblocktable>

</apex:pageBlock>
</apex:form>
</apex:page>

 

Best Answer chosen by Admin (Salesforce Developers) 
ForcepowerForcepower

Pancho,

 

A couple of observations:

 

Your indexes for your loops should start from 0 rather than 1.

I'm surprised that the where expression is working as-is for you - where name =:zips. I would have used "where name IN :zips"

 

Make those index changes and see if you still get an out of bounds exception. You may also want to put some Debug statements to see what zips are coming back

 

for (Integer i=0;i<parsedCSV.size();i++)

        {

        zips.addparsedCSV[i][6].replaceAll('\"',''));

        }

        List<Zip_Code_Master__c> zipList= [select id from Zip_Code_Master__c where name = :zips];

       

       System.debug('zipList size = ' + zipList.size());

       

        for (Integer i=0;i<parsedCSV.size();i++)

        {

All Answers

ForcepowerForcepower

Pancho,

 

It looks like you're trying to assign to a list item without actually creating that item first:

 

 

        zips[i] = parsedCSV[i][6].replaceAll('\"','');

instead, try

       zips.add(parsedCSV[i][6].replaceAll('\"',''));

Ram

PanchoPancho

Thanks Ram,

You are right.

I made that change and I am still getting:

 

System.ListException: List index out of bounds: 17

Error is in expression '{!ReadFile}' in page fotaupload

Class.FOTAuploader.ReadFile: line 122, column 1     

 

 

ForcepowerForcepower

Pancho,

 

Can you post your line 122?

PanchoPancho

 

Line 122 is:

 

a.Zip_Code_of_Activation__c = zipList[i].id;

 

 

I also tried:

if (zipList[i]!=null) {
a.Zip_Code_of_Activation__c = zipList[i].id;
}

 

but still get the same error.

ForcepowerForcepower
ok - it looks like you have some 16 or so zip codes in your table matching [select id from Zip_Code_Master__c where name =:zips];
I don't know too much about your matching process for the CSV to zipcode. Is there supposed to Zip code matching yp with your CSV file line?
PanchoPancho

Yes

My test csv file has 17 lines in it.

On of the fields, the 6th column has a text zip code.

Instead of just importing the zip code, I have a related object called Zip Code, so I am wanting to do a lookup of the id of the zip code and store the id into a.Zip_Code_of_Activation__c.

 

 

ForcepowerForcepower

Pancho,

 

A couple of observations:

 

Your indexes for your loops should start from 0 rather than 1.

I'm surprised that the where expression is working as-is for you - where name =:zips. I would have used "where name IN :zips"

 

Make those index changes and see if you still get an out of bounds exception. You may also want to put some Debug statements to see what zips are coming back

 

for (Integer i=0;i<parsedCSV.size();i++)

        {

        zips.addparsedCSV[i][6].replaceAll('\"',''));

        }

        List<Zip_Code_Master__c> zipList= [select id from Zip_Code_Master__c where name = :zips];

       

       System.debug('zipList size = ' + zipList.size());

       

        for (Integer i=0;i<parsedCSV.size();i++)

        {

This was selected as the best answer
PanchoPancho

Ram,

You are awesome!  Thank you so much for your help.

 

That fixed the problem.  I think I had it set to i=1 to bypass the header(the first line of the CSV file).

The index out of bounds error is gone!

 

There is just one more thing, if you dont mind helping me figure out.

 

It shows that I am getting the lookup to work, but the two arrays I have are not in synch.

I have the parsedCSV array that correctly imports the CSV file with rows and columns (parsedCSV[row][column])

I also have the zipList array that is storing the IDs to the zipcodes.

 

But they are not lining up. I hope that makes sense.  The zipcodes are in a random order it appears.  I have the right ones but wrong order.

 

 

 

ForcepowerForcepower

Pancho,

 

Got it. Yes - the order of the zips is not guaranteed since you're not ordering it. I'd suggest loading the fetched zips to a Map<String, Id> zipMap with the key being the zip code and the value being the id. In your loop parsing the CSV, just do a

 

List<Zip_Code_Master__c> zipList= [select id, name from Zip_Code_Master__c where name =:zips];

Map<String, Id> zipMap = new Map<String, Id>();

for (Zip_Code_Master__c zip : zipList) {

    zipMap.put(zip.name, id);

}

    

 

a.Zip_Code_of_Activation__c = zipMap.get(parsedCSV[i][6].replaceAll('\"','');

 

Best,

Ram

PanchoPancho

Ram,

That worked!

Thank you so much amigo!

God bless you my friend.

 

Pancho

ForcepowerForcepower

Pancho,

Glad to hear it - you're very welcome, my friend!

Best!

Ram