OraFAQ Forum: Server Utilities » sqlldr and To

Home » RDBMS Server » Server Utilities » sqlldr and To_date function not working

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sqlldr and To_date function not working [message #255989]

Thu, 02 August 2007 12:35

Deano252
Messages: 2
Registered: August 2007
Location: Westerly, RI

Junior Member

Hi All,

I have an interesting problem. I need to load a bunch of legacy data into oracle. The problem I'm faced with is even if I have the correct date mask specified the data does not get loaded.

I have pasted my control file below

LOAD DATA
INFILE 'C:\legacyRM\migrate.txt'
BADFILE 'C:\legacyRM\migrate.BAD'
DISCARDFILE 'C:\legacyRM\migrate.DIS'
APPEND
INTO TABLE legacyRM
FIELDS TERMINATED BY ' '
TRAILING NULLCOLS
(name,
study_date "to_date (:study_date, 'mm/dd/yyyy hh:mi:ss am')",
dob "to_date (:dob, 'mm/dd/yyyy')",
path)

The bold part is the mask I changed

my tab delimited file that I'm using to load this data has the following in the corresponding study_date field

Name 9/26/1999 4/8/2002 9:41:20 AM Path
Name 1/31/2005 6/27/2005 12:00:23 PM Path

and the bold section is the date that is relevant to the control file.

My to_date function looks correct and if I go into my tab delimited file and edit out the time from the study date keeping only the MM/DD/YYYY it will load.

I'm kind of baffled Confused

Any help is appreciated..
Thanks,
Deano

Report message to a moderator

Re: sqlldr and To_date function not working [message #255990 is a reply to message #255989]

Thu, 02 August 2007 12:43

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

http://www.orafaq.com/forum/m/253790/105911/?srch=SQLLDR#msg_253790

Report message to a moderator

Re: sqlldr and To_date function not working [message #255991 is a reply to message #255989]

Thu, 02 August 2007 12:43

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

the bold section is the date that is relevant to the control file.

No.
study_date in the second field that is the one WITHOUT time.
The third field (the one in bold) is for dob which does not care about time.

Regards
Michel

Report message to a moderator

Re: sqlldr and To_date function not working [message #255997 is a reply to message #255991]

Thu, 02 August 2007 12:55

Deano252
Messages: 2
Registered: August 2007
Location: Westerly, RI

Junior Member

Ok thats working now. Man I should have saw that. It's been a long day

Thanks,
Deano

Report message to a moderator

Previous Topic:	Why did the dump file extracted too large?
Next Topic:	FNDLOAD is not loading non-mandatory parameters

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 22 22:06:51 CDT 2024