Setting the Date Format in the SQL*Loader Control File

oracle sql-loader to-date
Angelina picture Angelina · May 16, 2013 · Viewed 63.3k times · Source

I have csv file that has "17 September, 2009 11:06:06 AM" as COMPLETED_ON variable

I am using sql loader to load data to oracle with folowing:

LOAD DATA
INFILE 'c:/load/file_name.csv'
APPEND
INTO TABLE tbl_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
COMPLETED_ON DATE "not sure what to put here",
)

in oracle I have this column created as follows:

"COMPLETED_ON" TIMESTAMP (0) WITH LOCAL TIME ZONE

How do I change COMPLETED_ON date in control file?

Answer

Ed Gibbs picture Ed Gibbs · May 16, 2013

I'm not 100% with the LOAD DATA INFILE syntax, but I know the format string 'DD Month, YYYY HH:MI:SS AM' matches your date format. I was able to use it in a TO_DATE to convert your sample date. Try this:

LOAD DATA
INFILE 'c:/load/CW_COMPLIANCE.csv'
APPEND
INTO TABLE tbl_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
COMPLETED_ON DATE 'DD Month, YYYY HH:MI:SS AM',
)

Related questions

Oracle: Import CSV file

I've been searching for a while now but can't seem to find answers so here goes... I've got a CSV file that I want to import into a table in Oracle (9i/10i). Later on I plan to use this …

Read More
Oracle sqlldr TRAILING NULLCOLS required, but why?

I have an abstruse sqlldr problem that's bothering me. My control file looks something like this: load data infile 'txgen.dat' into table TRANSACTION_NEW fields terminated by "," optionally enclosed by '"' TRAILING NULLCOLS ( A, B, C, D, ID "…

Read More
Disable and later enable all table indexes in Oracle

How would I disable and later enable all indexes in a given schema/database in Oracle? Note: This is to make sqlldr run faster.

Read More