Date/time format issue - SQL loader

Hello,

I am trying to import data from CSV file into Oracle database table using SQL loader. I am getting date/time format error.

I am attaching the CTL file, CSV file and error message I get.

----------CTL
LOAD DATA
INFILE 'C:\PE2E\ToadCSVFile_2015-05-11T11_12_392015-05-11.csv'
BADFILE 'C:\PE2E\ToadCSVFile_2015-05-11T11_12_392015-05-11.bad'
DISCARDFILE 'C:\PE2E\ToadCSVFile_2015-05-11T11_12_392015-05-11.dsc'

INTO TABLE "DQADMIN"."DQ_TEST"
FIELDS TERMINATED BY ','
(BEGIN_EFFECTIVE_DT DATE)


-----Target table structure
CREATE TABLE DQADMIN.DQ_TEST
(
  BEGIN_EFFECTIVE_DT  DATE                      NOT NULL
)



Please assist
Thank you
test.log
ToadCSVFile-2015-05-11T11-12-392015-05-1
angel7170Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeOM_DBACommented:
Try:
LOAD DATA
OPTIONS(SKIP=1)
INFILE 'C:\PE2E\ToadCSVFile_2015-05-11T11_12_392015-05-11.csv'
BADFILE 'C:\PE2E\ToadCSVFile_2015-05-11T11_12_392015-05-11.bad'
DISCARDFILE 'C:\PE2E
INTO TABLE "DQADMIN"."DQ_TEST"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(BEGIN_EFFECTIVE_DT DATE  "TO_DATE(:BEGIN_EFFECTIVE_DT DATE,'YYYY-MM-DD HH24:MI:SS'))

Open in new window

angel7170Author Commented:
Thank you!

I get an error saying "date format not recognized"
MikeOM_DBACommented:
OK try this one:
OPTIONS ( SKIP=1 )
LOAD DATA
INFILE 'C:\PE2E\ToadCSVFile_2015-05-11T11_12_392015-05-11.csv'
BADFILE 'C:\PE2E\ToadCSVFile_2015-05-11T11_12_392015-05-11.bad'
DISCARDFILE 'C:\PE2E
TRUNCATE INTO TABLE "SCOTT"."DQ_TEST"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(BEGIN_EFFECTIVE_DT  DATE 'YYYY-MM-DD\ HH24:MI:SS'
)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

awking00Information Technology SpecialistCommented:
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' WILL LOOK FOR DATA LIKE "2011-08-24 23:20:24", But you don't seem to have any commas in your data, try just
FIELDS TERMINATED BY WHITESPACE OPTIONALLY ENCLOSED BY '"'
Note - that's a single quote followed by a double quote followed by a single quote and DO use the quoted to_date function that MikeOM_DBA has shown.
Mark GeerlingsDatabase AdministratorCommented:
I usually use a multi-step approach to the problem of messy data in a "date" field that I need to load into an Oracle "date" column.  I create a work table with all varchar2 columns and use SQL*Loader to populate the work table.  Then, I review and clean up the data, if necessary, in the work table so it is all consistent.  Then, I use a simple "insert into [target table] select ... from [work table]" command (with whatever "to_date" conversion is required, now that the data is consistent) to actually load the target table.

If this will be a repeated data load problem, I usually create a PL\SQL procedure with a few different update commands to clean up the commonly-occurring data problems, and report the few exceptions that still fail, so they can be fixed manually.
johnsoneSenior Oracle DBACommented:
I tested with this control file.  It loaded all the records without error.  Very similar to the one posted by MikeOM, so that one may work too.

OPTIONS (SKIP=1)
LOAD DATA
	INFILE 'C:\PE2E\ToadCSVFile_2015-05-11T11_12_392015-05-11.csv' 
	BADFILE 'C:\PE2E\ToadCSVFile_2015-05-11T11_12_392015-05-11.bad'
	DISCARDFILE 'C:\PE2E\ToadCSVFile_2015-05-11T11_12_392015-05-11.dsc'	DISCARDMAX 9999
	APPEND INTO TABLE dq_test
	fields terminated by "," optionally enclosed by '"'
	trailing NULLCOLS
	(
		BEGIN_EFFECTIVE_DT DATE 'YYYY-MM-DD HH24:MI:SS'
	)

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.