Can't load boolean values from csv into table in db2

Question

Trying to load csv with boolean column that has entries like this 1,10044.71556451,True

When using LOAD command I get the error SQL27924W The value in row and column 3 could not be converted to a Boolean value, and the target column is not nullable, so the row was not loaded. But when I try to INSERT it manually, it is inserted as expected. Why isn't the LOAD command working in this case?

The LOAD command that gives errors

LOAD FROM '/csvs/loans.csv' OF DEL MODIFIED BY NOHEADER ANYORDER INSERT INTO loans

The INSERT command that works

INSERT INTO loans (loan_id, amount, accepted) VALUES (1,10044.71556451,True)

Add the table schema and Db2 version – data_henrik Jul 16 '19 at 11:45 — data_henrik, Jul 16 '19 at 11:45
Is 10044.71556451 true or false? Why? – mustaccio Jul 17 '19 at 01:45 — mustaccio, Jul 17 '19 at 01:45

score 2 · Answer 1 · answered Jul 16 '19 at 11:59

2

If your Db2-server runs on Linux/Unix/Windows, the load or ingest/import commands may support only values 0 and 1 for target-column type BOOLEAN. This seems to be the case up to version 11.1.4.4a. I did not try on version 11.5 yet.

You could preprocess the file, or arrange that the CSV file gets created with supported literal values. You might also be able to transform during ingest.

answered Jul 16 '19 at 11:59

mao

11,321
2
13
29

I am using 11.5 on linux yes. Couldn't find a way to convert during loading but I managed to do it by changing the datatype of the column to be VARCHAR then loaded the data and then altered the datatype to BOOLEAN again and it worked. – Omar Ahmad Jul 16 '19 at 13:27

Can't load boolean values from csv into table in db2

1 Answers1