Trying to parse csv file where some COLUMN cells are empty

Question

I am trying to read a .csv file which has 6 columns, but some rows in the last column is empty cell.

item1   2   4   125 200 
item2   5   8   250 375 
item3   2   3   125 200 
item4   2   4   200 325 10
item5   1   2   325 400 10
item6   1   2   250 350 10

Result

row 0: item1
row 1: 2.0
row 2: 4.0
row 3: 125.0
row 4: 200.0
new --------: 0
row 0: item2
row 1: 5.0
row 2: 8.0
row 3: 250.0
row 4: 375.0
new --------: 0
row 0: item3
row 1: 2.0
row 2: 3.0
row 3: 125.0
row 4: 200.0

Java code should get all the rows, parse each value into double.

The last column if its empty, print a 0 value, otherwise parse the last column value

QUESTION: How to i get empty cell value for rows and set int lastColEmpytyValue = 0;?

    while(inputStream.hasNext()) {
    String data = inputStream.nextLine(); // gets a while line .next gets next word i.e. ice. .nextLine get word with spaces i.e. ice cream

        String[] row = data.split(",", -1); // regex - split line

    System.out.println("row 0: " + row[0] ); 
    System.out.println("row 1: " + Double.parseDouble(row[1] )); 
    System.out.println("row 2: " + Double.parseDouble(row[2]) ); 
    System.out.println("row 3: " + Double.parseDouble(row[3]) ); 
    System.out.println("row 4: " + Double.parseDouble(row[4]) ); 
    if (row[5] == null || row[5].isEmpty() || row[5] == "") {
        int row5;
        row[5] = "0";
        row5 = Integer.parseInt(row[5]);
        System.out.println("new --------: " +row5);

    } else {
        System.out.println("row 5.....: " + row[5]);

    }
}

You didn't ask a question. – cmosher01 May 04 '18 at 03:07 — cmosher01, May 04 '18 at 03:07
I do not see any data separated by commas – May 04 '18 at 15:54 — , May 04 '18 at 15:54

Red Boy · Accepted Answer · 2018-05-04T03:04:32.033

1

You have to just change the line, because I don't see ',' in your example data.

String[] row = data.split(",", -1);

to something like if you data has tab or space as separator.

//if you have "tabbed" separated data
String[] row = data.split("\t", -1);   
// if you have "space" separated data
//String[] row = data.split(" ", -1);

edited May 04 '18 at 03:04

answered May 04 '18 at 02:40

Red Boy

5,429
3
28
41

The issue with using an API is I am restricted to use it is I have to update class path.. I assume this would be required for csv? – Shaz May 04 '18 at 02:45

score 0 · Answer 2 · answered May 04 '18 at 03:27

The problem is you can't "get empty cell value" when there is no value. The split function will return only as many elements as exist in the string. So, in the case where there are only 5 values, it will return an array of length 5 (subscripts 0 through 4). And yet you are unconditionally trying to access position 5 in the array (in the if statement).

Instead, you should check the length of the array first, and if it's less than 5 then use 0 for the value.

Jeronimo Backes · Answer 3 · 2018-05-04T15:53:29.297

Can you use an external library to handle this for you reliably? If you can try univocity-parsers as it's easy and way faster:

CsvParserSettings settings = new CsvParserSettings();
settings.detectFormatAutomatically();
settings.setNullValue("0"); //if a value is null, convert it to 0

//always reads the first 6 columns
//you won't need this if the shorter rows with no data end with a tab
parserSettings.selectIndexes(0,1,2,3,4,5);

CsvParser parser = new CsvParser(settings);
//parse into Records as you can get primitive types from them.
List<Record> record = parser.parseAllRecords(file);

Hope this helps. Disclaimer: I'm the author of this library. It's open-source and free.

Trying to parse csv file where some COLUMN cells are empty

3 Answers3