CSVREAD: Quoted empty string should be empty string, not null

[marcusfey]

Maybe I miss a parameter, in this case I would be glad if you could tell me what to correct

We need to import CSV files into a table. The file contains double quotes for strings and commas as field separators. The table has varchar(x) not null columns. Thus we need to import empty strings as empty string and not as null. Therefore those empty strings are quoted in the file to be imported.

Thus something like this:

"A","B_not_null","C_nullable"
1,"foo",
2,"bar","x"
3,"","y"

B_not_null is not nullable, the empty string should go there. For C_nullable we want actual null values where the import contains a real empty column (no quotes).

If I try select * from csvread('sample.csv'); or select * from csvread('sample.csv',null,'fieldSeparator=,');

I get

A | B_NOT_NULL | C_NULLABLE
1 | foo        | null
2 | bar        | x                                                                                                                                                                                                                                                              
3 | null       | y

which I cannot insert into the table.

Is this desired behavior from H2 side and if so how do I solve my problem?

Thanks!

PS: I am aware there was a fix (#3786) in April regarding number columns and NULL. I tried the versions before and they did not work as well. I actually dispute the original problem (#3785) because I would argue that one would have to leave out the quotes if you need nulls. DB2 and PostgreSQL works like this.

[katzyn]

H2 2.2.220 has a known regression, but other versions aren't affected, you can use 2.1.214 or some older version.

This regression was already fixed, you can compile H2 from the current sources to test the fix.

The fix will be included into the next patch release.

[marcusfey2]

Thanks! I tried before using org.h2.tools.Sheel of an older version and still experienced the problem. It seems like I had to downgrade the already running server also to an older version.