CSV reading in java

Question

Im having trouble reading from a CSV file

final String DELIMITER = ",";
    Scanner fileScan = null;
    Scanner dataSetScan = null;
    String dataSet = null;
    String sql = "";
    File users = new File("user.txt");
    String nickname = "";
    String lastname = "";
    String firstname = "";
    String cartype = "";
    String personimage = "";
    String carimage = "";
    int user_id = 0;

    try {
        fileScan = new Scanner(users);
    } catch (Exception e) {
        System.out.println(e);
    }

while(fileScan.hasNext()){
        dataSet = fileScan.nextLine();
        dataSetScan = new Scanner(dataSet);
        dataSetScan.useDelimiter(DELIMITER);

        nickname = dataSetScan.next();
        lastname = dataSetScan.next();
        firstname = dataSetScan.next();
        cartype = dataSetScan.next();
        personimage = dataSetScan.next();
        carimage = dataSetScan.next();

        sql += "INSERT INTO users VALUES (";
        sql += user_id++ + ", ";
        sql += "'" + nickname + "', ";
        sql += "'" + lastname + "', ";
        sql += "'" + firstname + "', ";
        sql += "'" + cartype + "', ";
        sql += "'" + personimage + "', ";
        sql += "'" + carimage + "' ";
        sql += ");\n";

    }

The above code wont work on the example file

alice,Wonder-Land,Alice,red Vauxhall Corsa,alice.jpg,alice_car.jpg
bob,Kett,Robert,,,
charlie,Carlos,Don,,,

However, it works just fine when there is a comma at the end of the line. (hvaing a comma here is not an option)

What can i do to make this work? It must be to do with my delimeter i think

Thank you

Answer 1

Are you getting a NoSuchElementException from the following line?

carimage = dataSetScan.next();

If so you just need to wrap that with a hasNext check and perform a null check when you build your string.

if(dataSetScanner.hasNext()){
    carimage = dataSetScan.next();
}
else{
    carimage =  null;
}

...

sql += carimage  == null ? "NULL" : "'" + carimage + "' ";

Answer 2

I would recommend testing each token before inserting it,

But to answer your question, add an if condition before the last dataSetScan.next() call like so:

        if (dataSetScan.hasNext()){
            carimage = dataSetScan.next();
        }

Answer 3

I wouldn't recommend using your own parser for CSV. CSV is surprisingly complex with little gotchas everywhere.

For instance, in CSV, it is legal to quote a column value with a comma in it

3 columns in this file

abc,"value1,value2",def

I recommend this library for java, it's very easy to use.

http://opencsv.sourceforge.net/

EDIT - May 2013

Since writing this post, I have switched to this library, which supports the CSV "standard" better and is actively developed.

http://supercsv.sourceforge.net/