Daveo
Daveo

Reputation: 19872

db2 import csv with null date

I run this

 db2 "IMPORT FROM C:\my.csv OF DEL MODIFIED BY COLDEL, LOBSINFILE DATEFORMAT=\"D/MM/YYYY\" SKIPCOUNT 1 REPLACE INTO scratch.table_name"

However some of my rows have a empty date field so I get this error

SQL3191N which begins with """" does not match the user specified DATEFORMAT, TIMEFORMAT, or TIMESTAMPFORMAT. The row will be rejected.

My CSV file looks like this

"XX","25/10/1985"
"YY",""
"ZZ","25/10/1985"

I realise if I insert charater instead of a blank string I could use NULL INDICATORS paramater. However I do not have access to change the CSV file. Is there a way to ignore import a blank string as a null?

Upvotes: 0

Views: 4335

Answers (2)

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11042

This is an error in your input file. DB2 differentiates between a NULL and a zero-length string. If you need to have NULL dates, a NULL would have no quotes at all, like:

"AA",

If you can't change the format of the input file, you have 2 options:

  • Insert your data into a staging table (changing the DATE column to a char) and then using SQL to populate the ultimate target table

  • Write a program to parse ("fix") the input file and then import the resulting fixed data. You can often do this without having to write the entire file out to disk – your program could write to a named pipe, and the DB2 IMPORT (and LOAD) utility is capable of reading from named pipes.

Upvotes: 1

Clockwork-Muse
Clockwork-Muse

Reputation: 13056

I'm not aware of anything. Yes, ideally that date field should be null.

Probably the best thing to do would be load the data into a scratch/temp table where that isn't a date column - just leave it as character data (it looks like you're already using a scratch table anyways). It should be trivial after that to use a CASE statement to transform the information into a null date if the value is blank, when doing your INSERT to the real table.

Upvotes: 1

Related Questions