read csv file with a delimiter specified in the value itself

Question

The below example is a record from csv file which is seperated by a comma delimiter However there are chances when a particular column has a comma in-itself like the one below which is show in double quotes " " in the csv file.

Example from the csv file: Column1, Column2, "Column, 3",-,1G9937

When i try to read this file using my java code at the third position it just prints Column and not Column, 3 how can i tackle such cases ?

String cvsSplitBy = ",";

try {

    br = new BufferedReader(new FileReader(csvFile));
    while ((line = br.readLine()) != null) {

            // use comma as separator
        String[] businessdirection = line.split(cvsSplitBy);

        System.out.println("FuncVP = " + businessdirection[0]   + " , VP=" + businessdirection[1] + " , VP=" + businessdirection[2]+ " , VP=" + businessdirection[3] + " , VP=" + businessdirection[4]+ " , VP=" + businessdirection[5]);

Looks like this might be of help: http://stackoverflow.com/questions/1757065/splitting-a-comma-separated-string-but-ignoring-commas-in-quotes — Alex Yuly, Mar 12 '14 at 06:00

score 1 · Answer 1 · answered Mar 12 '14 at 06:00

Use an existing CSV reader library, like OpenCSV, or another one. There are many aspects about CSV that are more difficult than you would think of at first, such as commas or new lines inside values. You cannot parse it just by splitting on commas, and you cannot even assume that one line in the text file represents one row of data.

score 1 · Answer 2 · answered Mar 12 '14 at 06:01

This might not be the only case you need to handle. Why dont you use a thrid party CSV parsing util. I have used opencsv earlier and it seemed to work quite well for all the scenarios.

http://sourceforge.net/projects/opencsv/

http://opencsv.sourceforge.net/

score 1 · Accepted Answer · answered Mar 12 '14 at 06:02

You cannot just simply use split to parse CSV file. You can use a library (e.g. SuperCSV or OpenCSV) or you will have to follow all the CSV format rules. For instance, here you should ignore the delimiter (i.e. comma) between the double quotes (You can use regex for this).

read csv file with a delimiter specified in the value itself

3 Answers3