[SalesForce] Parsing comma separated data from the cell of csv file and storing into object

enter image description hereI am trying to parse a csv file and inserting data into custom object. One cell of csv file having comma seprated values.
This cell is not getting processed successfully.

 public string fieldSetName;
 public string fieldSetLabel;
 public string fieldSetdescription;
 public List<string> fieldSetAvailableFields;   

            nameFile =blobToString( contentFile,'ISO-8859-1');

            //Now sepatate every row of the excel file
            filelines = nameFile.split('\n');

            for (Integer i=1;i<filelines.size();i++)
            {
                String[] inputvalues = new String[]{};
                inputvalues = filelines[i].split(',');
                objectName = inputvalues[0].normalizeSpace();
                fieldSetName = inputvalues[1].normalizeSpace();
                fieldSetLabel = inputvalues[2].normalizeSpace();       
                fieldSetdescription = inputvalues[3].normalizeSpace();


                  for(integer j=4;j<inputvalues.size();j++){
                    System.debug('******inside the for loop***'+inputvalues[j]); // this showing only first value and getting error - loop should run 3 times to process first row's last cell.
                        fieldSetAvailableFields.add(inputvalues[j]);
                 }

            }

Best Answer

Your CSV parsing algorithm is overly simple. If you do 

            inputvalues = filelines[i].split(',');

your entire line will be split on the comma character, including any commas inside quoted cells. CSV is not a formal standard, but in most cases cells containing commas will be quoted:

Name,Id,Something__c
Test,001000000000001,"Some,values,here"

Your code will return ("Test", "001000000000001", "\"Some", "values", "here\""). When you get to fieldSetAvailableFields1 = inputvalues[j].split(',');, there's nothing further to split.

You'll need more sophisticated logic to handle this. One strategy is illustrated in the Custom Metadata Loader's CSV parser (which, by the way, does have some other issues) - while it walks the list of input fields, it tracks the presence of quotation marks to re-accumulate the original comma-separated values.

There are other routes, of course, - you could write a more traditional parser that walks token by token, or depending on what your actual data looks like just extract it with a regex - but the case is that you need more sophisticated logic to handle embedded commas.

In this case, if you can guarantee that only the very last column contains embedded commas, and that field is your availableFields, you can just rewrite your logic to take this into account.

            for(integer j=4;j<inputvalues.size();j++){
                fieldSetAvailableFields.add(inputvalues[j]);
            }

Just consume all of the remaining data in the row as field names. You may need to add logic to remove double-quotes starting or ending the field name, but this approach should work given the above constraint.

Related Topic