I am reading a CSV file through a named pipe. In the CSV file the field2 column is blank which need to be inserted into a table column as NULL. The table column is of type integer, but When I try to run the ingest
I am getting an error that says 'field2 cannot be converted to the value type: integer'.
Here is my below code
mkfifo mypipe
tail -n +2 myfile.csv > mypipe &
db2 "INGEST FROM FILE mypipe
FORMAT DELIMITED
(
$field1 CHAR(9),
$field2 INTEGER EXTERNAL,
$field3 CHAR(32)
)
INSERT INTO my_table
VALUES($field1, $field2, $field3)"
In the above code, $field2 will be blank. In the my_table, $field2 value doesn't get inserted as NULL when the field is blank in csv.
Sample input csv data as shown below
Subject_Name,Student_ID,STATUS
Maths,,COMPLETED
Physics,,PENDING
Computers,,PENDING
I want the data to be ingested in the table like below
Subject_Name|Student_id|STATUS |
------------|----------|---------|
Maths |NULL |COMPLETED|
------------|----------|---------|
Physics |NULL |PENDING |
------------|----------|---------|
Computers |NULL |PENDING |
------------|----------|---------|
Can anyone suggest a way to resolve this issue?