Parsing CSV in java

Question

I have this weird situation where I have to read horizontally. So I am getting a csv file which has data in horizontal format. Like below:

CompanyName,RunDate,10/27/2010,11/12/2010,11/27/2010,12/13/2010,12/27/2010....

All the dates shown after RunDate are values for run date field and I have to update that field for that company in my system. The date values are not fix number, they can be single value to 10 to n number. So I need to read all those values and update in the system. I am writing this in Java.

Answer 1

split them by "," and parse it and ,Use List to add all these values.

As other has suggested for splitting and parsing you can use opencsv

Answer 2

You start by reading the entire line into a String. Then you use the String.split(...) function to get all the tokens on the line where the delimiter you use is ",". (or is it "\," when you use a regex?)

Answer 3

In order to get each value one at a time, use a StringTokenizer. Construct it with StringTokenizer(str, ","). (Not recommended)

Use the split() method of the string class, which loads all of the tokens into an array.

Use the DateFormat class to parse each date -- specifically DateFormat.parse(String).

Answer 4

use java.util.Scanner - you can call useDelimiter() to make the comma your delimiter, and read new tokens with next(). The Scanner can be created directly from your file or a string read from the file.

Answer 5

Libraries like OpenCSV handle all the weird cases for CSV files (new lines, delimiting, etc).

Answer 6

A CSV file is a \n terminated file that each column can be seperated either by:

• Comma or
• Tabs \t

I suggest that you have a BufferedReader that reads the CSV file and use the readLine() method to read the row.

From each row, use String.split(arg) where arg will be your comma or tab \t to have an array of columns....from there, you know what to do.

Answer 7

By far the most useful page on the subject of CSV parsing I've ever found is the following:

http://secretgeek.net/csv_trouble.asp

Basically, get an established library to do it for you, because csv parsing is deceptively tricky.

Answer 8

String,split(",") isn't likely to work.
It will split fields that have embedded commas ("Foo, Inc.") even though they are a single field in the CSV line.

What if the company name is:
        Company, Inc.
or worse:
        Joe's "Good, Fast, and Cheap" Food

According to Wikipedia:    (http://en.wikipedia.org/wiki/Comma-separated_values)

Fields with embedded commas must be enclosed within double-quote characters.

   1997,Ford,E350,"Super, luxurious truck"

Fields with embedded double-quote characters must be enclosed within double-quote characters, and each of the embedded double-quote characters must be represented by a pair of double-quote characters.

   1997,Ford,E350,"Super ""luxurious"" truck"

Even worse, quoted fields may have embedded line breaks (newlines; "\n"):

Fields with embedded line breaks must be enclosed within double-quote characters.

   1997,Ford,E350,"Go get one now  
   they are going fast"

This demonstrates the problem with String,split(",") parsing commas:

The CSV line is:

a,b,c,"Company, Inc.", d, e,"Joe's ""Good, Fast, and Cheap"" Food", f, 10/11/2010,1/1/2011, g, h, i

// Test String.split(",") against CSV with
// embedded commas and embedded double-quotes in
// quoted text strings:
//
// Company names are:
//        Company, Inc.
//        Joe's "Good, Fast, and Cheap" Food
//
// Which should be formatted in a CSV file as:
//        "Company, Inc."
//        "Joe's ""Good, Fast, and Cheap"" Food"
//
//
public class TestSplit {
    public static void TestSplit(String s, String splitchar) {
        String[] split_s    = s.split(splitchar);

        for (String seg : split_s) {
            System.out.println(seg);
        }
    }


    public static void main(String[] args) {
        String csvLine = "a,b,c,\"Company, Inc.\", d,"
                            + " e,\"Joe's \"\"Good, Fast,"
                            + " and Cheap\"\" Food\", f,"
                            + " 10/11/2010,1/1/2011, h, i";

        System.out.println("CSV line is:\n" + csvLine + "\n\n");
        TestSplit(csvLine, ",");
    }
}

Produces the following:

D:\projects\TestSplit>javac TestSplit.java

D:\projects\TestSplit>java  TestSplit
CSV line is:
a,b,c,"Company, Inc.", d, e,"Joe's ""Good, Fast, and Cheap"" Food", f, 10/11/2010,1/1/2011, g, h, i


a
b
c
"Company
 Inc."
 d
 e
"Joe's ""Good
 Fast
 and Cheap"" Food"
 f
 10/11/2010
1/1/2011
 g
 h
 i

D:\projects\TestSplit>

Where that CSV line should be parsed as:

a
b
c
"Company, Inc."
 d
 e
"Joe's ""Good, Fast, and Cheap"" Food"
 f
 10/11/2010
1/1/2011
 g
 h
 i