topic Re: date problem with sqlldr in Operating System - HP-UX

date problem with sqlldr

jane zhang — Fri, 26 Oct 2001 18:48:33 GMT

Hi
I have data date.dat file including the following data
20011025
20011025
20011025
20011025

I need to loaded into oracle table ABC which has a create_date column of type date.

The control file I use is like
load data
infile '/home/janez/date.dat'
append
into table ABC
fields terminated by " "
(CREATION_DATE )

It won't work since the sysdate data format is 25-OCT-01. I can not reset nls-date-format parameter since other users are using this format to import data.
Can anybody give me some suggestion, or maybe we can do something about the control file?

Thank you for the help,

Re: date problem with sqlldr

Andreas D. Skjervold — Fri, 26 Oct 2001 19:09:54 GMT

Hi
Here is what to do:
load data
infile '/home/janez/date.dat'
append
into table ABC
fields terminated by " "
(CREATION_DATE ???yyyy-mm-dd???)

Andreas

Re: date problem with sqlldr

Andreas D. Skjervold — Fri, 26 Oct 2001 19:11:28 GMT

Ooops couldn't get it right...

load data
infile '/home/janez/date.dat'
append
into table ABC
fields terminated by " "
(CREATION_DATE DATE ???yyyy-mm-dd???)

Andreas

Re: date problem with sqlldr

Andreas D. Skjervold — Fri, 26 Oct 2001 19:19:46 GMT

This is almost embarrasing;

The correct datemask is "YYYYMMDD" not "yyyy-mm-dd" is I typed: That would be for 2001-10-25 dates...

Andreas

Finaly got it right!

Re: date problem with sqlldr

jane zhang — Fri, 26 Oct 2001 19:45:01 GMT

Andreas,
Thanks for the solution, it works!
May I ask you a further question? How Can I import the data without the header column?
for example,
the data file looks like,
DATE
20011025
20011025
20011025
20011025
The first line will give error since DATE is not a date value.
I tried to "skip 1" in the control file, but it did like it either.
Regards,
Jane

Re: date problem with sqlldr

jane zhang — Sat, 27 Oct 2001 22:43:06 GMT

Hi,
I used skip=1 as command line parameter, sqlldr skipped the first header line.
thanks,
Jane

Re: date problem with sqlldr

Andreas D. Skjervold — Sun, 28 Oct 2001 14:02:45 GMT

Hi

I usually depend on sqlloader to skip the header row itself due to data inconsistency, but ofcourse your way is better.

Andreas