What cellprocessor to use on strings when using SuperCSV CSVBeanReader

Question

@Carl V. Dango ,@Zsolt, @Jon, Let's start over. Here is my code and error (at bottom). You'll notice in the console output the first two lines of raw data. The first field in the first row ("item") is null in the output. I thought it should be 'xx' because I had the StrReplace(" ","xx") coded on that column. My bigger worry is how to trap a null/blank in column 4 on the third record. These seems so basic that I'm a little dismayed it's this much trouble to solve.

package com.mycompany.data.transfers.app;

import java.io.FileReader;

import org.supercsv.cellprocessor.ConvertNullTo;
import org.supercsv.cellprocessor.ParseBigDecimal;
import org.supercsv.cellprocessor.ParseDate;
import org.supercsv.cellprocessor.ParseInt;
import org.supercsv.cellprocessor.ift.CellProcessor;
import org.supercsv.io.CsvBeanReader;
import org.supercsv.prefs.CsvPreference;

import com.mycompany.data.transfers.models.Invoice;

public class Test {

/**
 * @param args
 * @throws Exception 
 */
public static void main(String[] args) throws Exception {

    char quote = '"';
    int delimeter = 124;
    String newLine = "\n";

    CellProcessor[] cp = new CellProcessor[] { 
            new StrReplace(" ", "xx"),
            null,
            new ParseDate("yyyyMMdd"), 
            new ParseBigDecimal(),
            new ConvertNullTo("0", new ParseBigDecimal()),
            new ParseDate("yyyyMMdd"), 
            null,
            new ParseDate("yyyyMMdd"), 
            null,
            null,
            null,
            null,
            null,
            new ParseInt() 
        };

    Invoice bean = new Invoice();

    CsvBeanReader inFile = new CsvBeanReader(new FileReader("c:\\temp\\my_test_file.txt"), new CsvPreference(quote, delimeter, newLine));

    while ((bean = inFile.read(bean.getClass(), new String[] {"item", "loc", "schedDate", "fracQty", "recQty",
        "expDate", "poNum", "dateShip", "masterLoadId", "loadId",
        "confirmationNumber", "sourceWarehouse", "purchasingGroup",
        "poDistFlag" }, cp)) != null) {
        System.out.println(bean);
    }
}

}

Invoice [item=, loc=NEW, schedDate=Wed Nov 02 00:00:00 CDT 2011, fracQty=5, recQty=4]
Invoice [item=0006268410, loc=SHR, schedDate=Thu Nov 03 00:00:00 CDT 2011, fracQty=12, recQty=5]
Exception in thread "main" null
Parser error context: Line: 3 Column: 4 Raw line:
[0000939515, NEW, 20111102, 50, , 20111102, , 20111102, 0000000000, 0000000000, , , BBA, 1]
 offending processor: org.supercsv.cellprocessor.ParseBigDecimal@17a8913
at org.supercsv.cellprocessor.ParseBigDecimal.execute(Unknown Source)
at org.supercsv.cellprocessor.ConvertNullTo.execute(Unknown Source)
at org.supercsv.util.Util.processStringList(Unknown Source)
at org.supercsv.io.CsvBeanReader.read(Unknown Source)
at com.mycompany.data.transfers.app.Test.main(Test.java:48)
Caused by: java.lang.NumberFormatException
at java.math.BigDecimal.<init>(BigDecimal.java:534)
at java.math.BigDecimal.<init>(BigDecimal.java:728)
... 5 more

Please post the code where you are trying to use StrReplace(" ", "XXxx"). — Zsolt Török, Nov 29 '11 at 09:49
@Zsolt This is my first posting here... I'm not sure if I post my code in the 'comment' area or 'answer your question'....but here it is. I apologize in advance for the formatting. When I press Enter, the comment takes. I can't add line breaks. — Davidson, Nov 29 '11 at 18:49
Edit your question you posted above, click on edit on the lower left hand corner of it (just below the question's tags). Put the code at the bottom of your questions and be sure to format it so we can read it easily (select the code you've pasted and click on the code button at the top of the editor). — Jon, Nov 29 '11 at 21:43
@Davidson One more suggestion: add the complete class to the bottom of your question, as opposed to just a few lines of code. Makes it easier for others to diagnose the problem. — Zsolt Török, Nov 30 '11 at 08:24
Could you please provide the error as well? Also I found a question where someone was having the same problem as you and switched to CSVListReader, and that worked for them. You might want to check that question out: http://stackoverflow.com/questions/8289855/parsing-strings-in-supercsv — Jon, Nov 30 '11 at 19:39
@Jon ifyou look in the link you pasted, you'll see the error *and* the row of data it's trying to parse. This page wouldn't allow me to past more code...kept telling to mark it as code no matter how many times I tried.... Geewhiz.... I just want see StrReplace(..) work, see a blank converted converted to a 0 so it won't fail on ParseBigDecimal.... — Davidson, Dec 01 '11 at 15:23
I think I figured it out. The column in the csv file has to actually be a single blank with quotes for new StrReplace(" ", "xx") to work. It won't work if the column is 10 blanks or if there are no quotes (i.e. just ||..... my delimeter was the pipe. It's very exact. Also, as for the numbers, I went to the group and had them give me zeros and not empty cells in the numeric columns. SuperCSV is a good product, but it's not magical--I was expecting too much, I believe. Thanks for everybody who had the patience to read all my clumsy posts. — Davidson, Dec 01 '11 at 20:36
@Zsolt, did ya'll decide that you don't have an answer for handling empty strings in a csv record where that column is a non-string field? Thanks. — Davidson, Dec 15 '11 at 23:40

score 2 · Answer 1 · edited Dec 14 '18 at 23:02

This issue here is that CsvBeanReader does not convert an empty column to null, but to "" (empty String). Therefore you should only really need the ConvertNullTo processor when writing CSV files.

So instead of

new ConvertNullTo("0", new ParseBigDecimal())

you should use

new Token("", "0", new ParseBigDecimal()) // replace "" with "0"

That's assuming you want a BigDecimal with a value of zero for blank columns - if you want it to be null instead then use

new Optional(new ParseBigDecimal())

I'll admit the site and javadoc need to make this more explicit, which is something I'm working on for the upcoming release of Super CSV.

Edit: Update for Super CSV 2.0.0-beta-1

The recently released Super CSV 2.0.0-beta-1 includes many bug fixes and new features (including Maven support and a new Dozer extension for mapping nested properties and arrays/Collections). It has also changed the treatment of nulls and empty string.

The new version reads "" (an empty column) as null, which means that you can now use your original code:

new ConvertNullTo("0", new ParseBigDecimal())

As a result of this, the Optional() cell processor now matches against null instead of "", which means you can also use it as a cell processor when writing (a null value will now be written as "").

Once again... thank you... Me, personally, I think SuperCSV does it all. Web examples are a litle on the thin side here and there, but over all, it's a very good product, and easy to use. — Davidson, Feb 14 '12 at 13:36
@Davidson Don't forget to select this as the correct answer :) — James Bassett, Nov 05 '12 at 06:43

What cellprocessor to use on strings when using SuperCSV CSVBeanReader

1 Answers1