0

I use sqlldr to import CSV files and I have some problem with date multiple formats.

Dates inside the CSV file are DD/MM/YYYY and if there is no date it is a single dot

CSV file

DATE_COLUMN;OTHER_COLUMN
01/01/2013;other column content 1
.;other column content 2

My .ctl file for sqlldr

LOAD DATA
INFILE '/path/to/my/file.csv'
REPLACE INTO TABLE table_to_fill
FIELDS TERMINATED BY ';'
(
COLUMNDATE "decode(:COLUMNDATE ,NULL,'.', to_date(:COLUMNDATE ,'DD/MM/YYYY'))",
OTHER_COLUMN
)

The import is working when I use :

decode(:COLUMNDATE ,NULL,'.'))

or

to_date(:COLUMNDATE ,'DD/MM/YYYY')

But not when I try to combine both...

Here is the error log :

Record 1: Rejected - Error on table table_to_fill, column COLUMNDATE.
ORA-01858: a non-numeric character was found where a numeric was expected

How can I combine these, please ?

I thought that the last parameter of the "decode" function was for the default value of the column, am I wrong ?

kmas
  • 6,401
  • 13
  • 40
  • 62
  • 1
    May be switch `.` with `null` because value to compare with must come first in `decode`? `decode(:COLUMNDATE ,'.',NULL, to_date(:COLUMNDATE ,'DD/MM/YYYY'))` – ThinkJet Aug 08 '13 at 17:05
  • I've tried before because I thought like you, but every example I've seen was written this way. – kmas Aug 09 '13 at 07:46

1 Answers1

7

SQL Loader's "regular" syntax should be enough here. Try this:

LOAD DATA
INFILE '/path/to/my/file.csv'
REPLACE INTO TABLE table_to_fill
FIELDS TERMINATED BY ';'
(
  COLUMNDATE DATE(7) "DD/MM/YYYY" NULLIF COLUMNDATE = "."
  OTHER_COLUMN
)
Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69