2

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
ratherstrange
  • 117
  • 4
  • 10
  • 2
    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 Feb 10 '16 at 15:28
  • 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 Feb 10 '16 at 15:34
  • 2
    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 Feb 10 '16 at 15:36
  • 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 Feb 10 '16 at 15:39
  • 1
    `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 Feb 10 '16 at 15:44
  • @ratherstrange - you tried what exactly? .0 wouldn't be accepted (ORA-01821 again), so you'd need the fractional seconds FF instead. – Alex Poole Feb 10 '16 at 15:45
  • Okay.... I've now found that in oracle the format "yyyy-mm-dd HH24:MI:SS.FF" works but unfortunately this doesn't work in sqlldr - I'll update my question to reflect this. – ratherstrange Feb 10 '16 at 15:45
  • Ah, just saw your comments - I'm getting a bit of a delay. Thanks, you're right adding the fractional seconds works, but this format doesn't work for sqlldr (input in the place of the date format in my code above) – ratherstrange Feb 10 '16 at 15:46
  • Possibly [this question and answer](http://stackoverflow.com/questions/1980850/oracle-sqlldr-timestamp-format-headache) might be of use? Maybe you need `created_at timestamp 'DD/MM/YYYY HH24:MI:SS.ff1'`? – Boneist Feb 10 '16 at 15:46

1 Answers1

1

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'
)
Alex Poole
  • 183,384
  • 11
  • 179
  • 318