CODEWITHSUNDEEP
sqloracle-databaseformattingtimestampsql-loader
ratherstrange
ratherstrange

Reputation: 117

SQL*Loader/SQL date formatting issues

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:

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.

Upvotes: 2

Views: 3251

Answers (1)

Alex Poole
Alex Poole

Reputation: 191435

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'
)

Upvotes: 1

Related Questions