CSV parsing in Java - working example..?

Question

I want to write a program for a school java project to parse some CSV I do not know. I do know the datatype of each column - although I do not know the delimiter.

The problem I do not even marginally know how to fix is to parse Date or even DateTime Columns. They can be in one of many formats.

I found many libraries but have no clue which is the best for my needs: http://opencsv.sourceforge.net/ http://www.csvreader.com/java_csv.php http://supercsv.sourceforge.net/ http://flatpack.sourceforge.net/

The problem is I am a total java beginner. I am afraid non of those libraries can do what I need or I can't convince them to do it.

I bet there are a lot of people here who have code sample that could get me started in no time for what I need:

• automatically split in Columns (delimiter unknown, Columntypes are known)
• cast to Columntype (should cope with $, %, etc.)
• convert dates to Java Date or Calendar Objects

It would be nice to get as many code samples as possible by email.

Thanks a lot! AS

Answer 1

You also have the Apache Commons CSV library, maybe it does what you need. See the guide. Updated to Release 1.1 in 2014-11.

Also, for the foolproof edition, I think you'll need to code it yourself...through SimpleDateFormat you can choose your formats, and specify various types, if the Date isn't like any of your pre-thought types, it isn't a Date.

Answer 2

There is a serious problem with using

String[] strArr=line.split(",");

in order to parse CSV files, and that is because there can be commas within the data values, and in that case you must quote them, and ignore commas between quotes.

There is a very very simple way to parse this:

/**
* returns a row of values as a list
* returns null if you are past the end of the input stream
*/
public static List<String> parseLine(Reader r) throws Exception {
    int ch = r.read();
    while (ch == '\r') {
        //ignore linefeed chars wherever, particularly just before end of file
        ch = r.read();
    }
    if (ch<0) {
        return null;
    }
    Vector<String> store = new Vector<String>();
    StringBuffer curVal = new StringBuffer();
    boolean inquotes = false;
    boolean started = false;
    while (ch>=0) {
        if (inquotes) {
            started=true;
            if (ch == '\"') {
                inquotes = false;
            }
            else {
                curVal.append((char)ch);
            }
        }
        else {
            if (ch == '\"') {
                inquotes = true;
                if (started) {
                    // if this is the second quote in a value, add a quote
                    // this is for the double quote in the middle of a value
                    curVal.append('\"');
                }
            }
            else if (ch == ',') {
                store.add(curVal.toString());
                curVal = new StringBuffer();
                started = false;
            }
            else if (ch == '\r') {
                //ignore LF characters
            }
            else if (ch == '\n') {
                //end of a line, break out
                break;
            }
            else {
                curVal.append((char)ch);
            }
        }
        ch = r.read();
    }
    store.add(curVal.toString());
    return store;
}

There are many advantages to this approach. Note that each character is touched EXACTLY once. There is no reading ahead, pushing back in the buffer, etc. No searching ahead to the end of the line, and then copying the line before parsing. This parser works purely from the stream, and creates each string value once. It works on header lines, and data lines, you just deal with the returned list appropriate to that. You give it a reader, so the underlying stream has been converted to characters using any encoding you choose. The stream can come from any source: a file, a HTTP post, an HTTP get, and you parse the stream directly. This is a static method, so there is no object to create and configure, and when this returns, there is no memory being held.

You can find a full discussion of this code, and why this approach is preferred in my blog post on the subject: The Only Class You Need for CSV Files.

Answer 3

My approach would not be to start by writing your own API. Life's too short, and there are more pressing problems to solve. In this situation, I typically:

• Find a library that appears to do what I want. If one doesn't exist, then implement it.
• If a library does exist, but I'm not sure it'll be suitable for my needs, write a thin adapter API around it, so I can control how it's called. The adapter API expresses the API I need, and it maps those calls to the underlying API.
• If the library doesn't turn out to be suitable, I can swap another one in underneath the adapter API (whether it's another open source one or something I write myself) with a minimum of effort, without affecting the callers.

Start with something someone has already written. Odds are, it'll do what you want. You can always write your own later, if necessary. OpenCSV is as good a starting point as any.

Answer 4

i had to use a csv parser about 5 years ago. seems there are at least two csv standards: http://en.wikipedia.org/wiki/Comma-separated_values and what microsoft does in excel.

i found this libaray which eats both: http://ostermiller.org/utils/CSV.html, but afaik, it has no way of inferring what data type the columns were.

Answer 5

You might want to have a look at this specification for CSV. Bear in mind that there is no official recognized specification.

If you do not now the delimiter it will not be possible to do this so you have to find out somehow. If you can do a manual inspection of the file you should quickly be able to see what it is and hard code it in your program. If the delimiter can vary your only hope is to be able to deduce if from the formatting of the known data. When Excel imports CSV files it lets the user choose the delimiter and this is a solution you could use as well.

Answer 6

I agree with @Brian Clapper. I have used SuperCSV as a parser though I've had mixed results. I enjoy the versatility of it, but there are some situations within my own csv files for which I have not been able to reconcile "yet". I have faith in this product and would recommend it overall--I'm just missing something simple, no doubt, that I'm doing in my own implementation.

SuperCSV can parse the columns into various formats, do edits on the columns, etc. It's worth taking a look-see. It has examples as well, and easy to follow.

The one/only limitation I'm having is catching an 'empty' column and parsing it into an Integer or maybe a blank, etc. I'm getting null-pointer errors, but javadocs suggest each cellProcessor checks for nulls first. So, I'm blaming myself first, for now. :-)

Anyway, take a look at SuperCSV. http://supercsv.sourceforge.net/

Answer 7

Writing your own parser is fun, but likely you should have a look at Open CSV. It provides numerous ways of accessing the CSV and also allows to generate CSV. And it does handle escapes properly. As mentioned in another post, there is also a CSV-parsing lib in the Apache Commons, but that one isn't released yet.

Answer 8

At a minimum you are going to need to know the column delimiter.

Answer 9

Basically you will need to read the file line by line.

Then you will need to split each line by the delimiter, say a comma (CSV stands for comma-separated values), with

String[] strArr=line.split(",");

This will turn it into an array of strings which you can then manipulate, for example with

String name=strArr[0];
int yearOfBirth = Integer.valueOf(strArr[1]);
int monthOfBirth = Integer.valueOf(strArr[2]);
int dayOfBirth = Integer.valueOf(strArr[3]);
GregorianCalendar dob=new GregorianCalendar(yearOfBirth, monthOfBirth, dayOfBirth);
Student student=new Student(name, dob); //lets pretend you are creating instances of Student

You will need to do this for every line so wrap this code into a while loop. (If you don't know the delimiter just open the file in a text editor.)

Answer 10

I would recommend that you start by pulling your task apart into it's component parts.

1. Read string data from a CSV
2. Convert string data to appropriate format

Once you do that, it should be fairly trivial to use one of the libraries you link to (which most certainly will handle task #1). Then iterate through the returned values, and cast/convert each String value to the value you want.

If the question is how to convert strings to different objects, it's going to depend on what format you are starting with, and what format you want to wind up with.

DateFormat.parse(), for example, will parse dates from strings. See SimpleDateFormat for quickly constructing a DateFormat for a certain string representation. Integer.parseInt() will prase integers from strings.

Currency, you'll have to decide how you want to capture it. If you want to just capture as a float, then Float.parseFloat() will do the trick (just use String.replace() to remove all $ and commas before you parse it). Or you can parse into a BigDecimal (so you don't have rounding problems). There may be a better class for currency handling (I don't do much of that, so am not familiar with that area of the JDK).