2

Is there a way to configure how SuperCSV expects to handle NULL values? This is sometimes handled as:

,,

or as:

,NULL,

or as:

,null,

But I can't seem to find a way to configure how SuperCSV expects to handle these, especially for numeric columns.

robross0606
  • 544
  • 1
  • 9
  • 19
  • Looks like the empty column as null case should work in the next release: https://github.com/super-csv/super-csv/pull/84 – James Bassett Mar 04 '16 at 23:30

1 Answers1

6

,, is handled automatically by Super CSV. For example the following:

@Test
public void testNullsWithDefaultBehaviour() throws IOException{
    String csv = ",null,NULL\n";
    try (ICsvListReader reader = new CsvListReader(new StringReader(csv), 
        CsvPreference.STANDARD_PREFERENCE)){
        List<String> values = reader.read();
        for (String v : values){
            System.out.println(v == null);
        }
    }
}

Will print

true
false
false

You can make it handle "NULL" and "null" as well by using cell processors (a combination of Optional and Token). To make it more reusable, you can define your own processor which simply sets this up. This processor will check for null, then "null", then "NULL" and return null if it finds a match, otherwise it will return the value unchanged.

public class CustomOptional extends Optional {

    public CustomOptional(){
        super(new Token("null", null, new Token("NULL", null)));
    }


    public CustomOptional(final CellProcessor next) {
        super(new Token("null", null, new Token("NULL", null, next)));
    }

}

You can then use this processor

@Test
public void testNullsUsingProcessors() throws IOException{
    String csv = ",null,NULL\n";
    CellProcessor[] processors = {new CustomOptional(), 
                                  new CustomOptional(), 
                                  new CustomOptional()};
    try (ICsvListReader reader = new CsvListReader(new StringReader(csv), 
        CsvPreference.STANDARD_PREFERENCE)){
        List<Object> values = reader.read(processors);
        for (Object v : values){
            System.out.println(v == null);
        }
    }
}

Which will print 

true
true
true

To handle numeric columns, you can simply chain it together as you would with Optional:

new CustomOptional(new ParseInt())
James Bassett
  • 9,458
  • 4
  • 35
  • 68
  • I appreciate this answer, but view this as a semi-bug. Using ,, as the only means of representing NULL is problematic due to the difference between an empty string and a NULL string. Many database export systems specifically do NOT represent NULL in the only way that SuperCSV expects it by default. They use \N or NULL to represent NULL so you can discern between a null string and an empty string in situations where quotation marks are optional. – robross0606 Dec 02 '14 at 14:57
  • Yeah it's definitely a limitation with the CSV format. Most other formats (eg. JSON, YAML, XML) can distinguish between null and empty string without any hacky (non-portable) workarounds. – James Bassett Dec 02 '14 at 22:54
  • This solution also doesn't seem to work well when I'm reading into a Bean versus a list. What do you do if you have multiple columns that have NULL, but they are other data types than String (Double, Integer, etc.)? – robross0606 Dec 03 '14 at 18:59
  • Define `"doesn't seem to work well"` (you should get a meaningful exception). If the field is a Double, use `new CustomOptional(new ParseDouble())`, if it's an Integer, use `new CustomOptional(new ParseInt())`, and so on. You need to use the correct processor for the field's type. See [this page](http://supercsv.sourceforge.net/examples_reading.html) for an example. – James Bassett Dec 04 '14 at 04:15
  • I managed to get this working, though not exactly as described above. The problem I mentioned above was actually due to be using a SuperCSV extension which supports Annotations. It was not a SuperCSV problem itself, though I still think the recommended solution is very clunky in SuperCSV. SuperCSV should allow you to specify how it expects NULL to be represented versus making you create custom chained processors. – robross0606 Dec 05 '14 at 17:04