SQL*Loader/SQL date formatting issues

Question

I'm trying to create an automated process that will upload a file to a Oracle database daily.

I'm having issues with date formatting. In the CSV file which I need to upload, a date is formatted as "mm:ss.0". However when selecting that cell the formula bar shows the full date as a timestamp, eg "dd/mm/yyyy hh24:mi:ss". The column I'm trying to upload this into is TIMESTAMP(6).

I'm not sure what format to put in SQL*Loader. Currently this is my control file, which errors:

OPTIONS (SKIP=1)
LOAD DATA
INFILE 'C:\Users\test.csv'
INSERT INTO TABLE test
FIELDS TERMINATED BY "," 
TRAILING NULLCOLS
(
id integer,
created_at timestamp 'DD/MM/YYYY HH24:MI:SS',
)

Possibly wrongly, I'm currently working on the assumption that if I can find out what to put into the "Data Type" box in the manual Oracle SQL upload window this will also work for SQL*Loader. So far I've tried:

DD/MM/YYYY HH24:MI:SS - error: CREATED_AT GDK-05043: not a valid month
DD/MM/YYYY HH24:MI:SS.FF - error: CREATED_AT GDK-05043: not a valid month
MI:SS.0 - error: CREATED_AT GDK-05021: date format not recognized
MI:SS.FF - error: CREATED_AT GDK-05030: The date format pattern ends before converting entire input string.
HH24:MI:SS.FF error: CREATED_AT GDK-05030: The date format pattern ends before converting entire input string.

The file is getting sent to me via email, so I'm unable to change the format in Excel (while that works if I open the file and do it manually for one upload, the point is I want this to happen with no imput from me!)

It would be great if you'd be able to point me in the right direction.

Rather than open the file in excel, what do you see for that date field if you open the file in a text editor? — Boneist
I saw "yyyy-mm-dd HH24:MI:SS.0". Tried that in oracle and got the following error: "CREATED_AT GDK-05030: The date format pattern ends before converting entire input string. — ratherstrange
I want to know what you see inside the csv file when you open it in a text editor, not the error message you get after trying to load the data. EXCEL messes around with text/date/number formats, so what you see in EXCEL may not actually be what's in the file. — Boneist
Sorry, I pressed enter and the comment sent before I finished typing... Edited it to show the format I saw in notepad. And thanks very much for this tip - didn't know excel wouldn't show the format correctly. — ratherstrange
select to_timestamp('2016-02-10 15:42:12.3', 'yyyy-mm-dd hh24:mi:ss.ff') from dual; works fine for me; is all your data in that column of the same format? — Boneist

Alex Poole Alex Poole · Accepted Answer · 2016-02-10T15:53:52

The format @Boneist pointed you towards works in SQL*Loader; with a control file modified to:

OPTIONS (SKIP=1)
LOAD DATA
INFILE 'test.csv'
INSERT INTO TABLE test
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
id integer,
created_at timestamp 'YYYY-MM-DD HH24:MI:SS.FF'
)

And test.csv containing:

ID,Created at
1,2016-02-10 12:13:14.0

That loads a record fine:

Total logical records skipped:          1
Total logical records read:             1
Total logical records rejected:         0
Total logical records discarded:        0

Although it might not get the data you expect:

select * from test;

        ID CREATED_AT                 
---------- ----------------------------
 808594481 10-FEB-16 12.13.14.000000000

You probably didn't really mean integer there. If there is no transformation required you can just not specify the data type and let it default to CHAR and reply on implicit conversion to the table column data type; or if you want to specify it you need the external keyword:

...
(
id integer external,
created_at timestamp 'YYYY-MM-DD HH24:MI:SS.FF'
)

select * from test;

        ID CREATED_AT                 
---------- ----------------------------
         1 10-FEB-16 12.13.14.000000000

Or you can specify the transformation you want:

...
(
id "to_number(:id)",
created_at timestamp 'YYYY-MM-DD HH24:MI:SS.FF'
)

SQL*Loader/SQL date formatting issues

1 Answers