0

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?

vineeth
  • 641
  • 4
  • 11
  • 25
  • Either use a text file with your original command as is or escape the `$` characters with "\", if you want to run this command from a shell command prompt. – Mark Barinstein Aug 12 '21 at 13:36
  • yes @Mark I am using the '\' to escape the $ to run this command but the problem i am facing occurs when the command runs and cannot insert the blank as integer. Is there any way that i can replace the value as NULL when the field2 value is blank ? – vineeth Aug 12 '21 at 14:26
  • @vineeth Please edit your question to show exactly one sample line from the csv input. Specifically how the second column appears when blank. If it looks like `filed1,,field3` then it should work. But if the second column appears as `field1,'',field3` then it should fail. – mao Aug 12 '21 at 15:35
  • @mao I have edit the question to show the sample input csv and the expected output. Can you suggest some way to achieve this ? – vineeth Aug 12 '21 at 16:29
  • @mao i have edited the question with raw data. – vineeth Aug 12 '21 at 16:38
  • @vineeth, I cannot recreate your symptom, with that format. The ingest command in my case correctly converts the `,,` (the blank/empty field-2) into a NULL. What is the plain text output of the `db2level` command when run on the Db2-SERVER hostname? – mao Aug 12 '21 at 16:41

0 Answers0