Handle Commas In CSV Columns - Apex

Hi All,

I am going to cover a general scenario that arises when you are dealing with CSV files in Salesforce. With CSV file, you might have use case of adding file rows as records or match records on basis of some column value in CSV.

As a solution what generally we do, we convert blob into string and split it by new line to get all rows in a list and then by splitting all rows with comma one by one to get all respective columns. Nice and simple! But this can become tricky if you are getting commas in column value. Due this, your split logic will yield more number of columns as expected.

Scenario

Handling commas in column value

Solution

Rows should be changed slightly before splitting them into columns. One of the approach that I used is to replace commas with some keyword and then replace them back after split. Here is the code

//csvLine is a single row in string from CSV
//:quotes: here is keyword to replace
Integer index = csvLine.indexOf('"');
String csvLine = csvLine.substring(0, index) + ':quotes:' + csvLine.substring(index+1, csvLine.length()); 

You are thinking why indexOf is being used instead of replace method. This is because replace method will replace all commas including the ones which are seperating columns. Remember it is a CSV file, the row is a string with comma seperated values. So to tackle this, start point and end point of a column needs to be determined. By determining these, It would be every easy to replace column value commas that lie between these indexes. Every column that has comma in its value always have double quote(") in its start and at the end. These can be used for indexes.

Below is the full detailed working code :

Hope it helps! Happy Coding :)