Home » RDBMS Server » Server Utilities » sqlldr and To_date function not working
icon5.gif  sqlldr and To_date function not working [message #255989] Thu, 02 August 2007 12:35 Go to next message
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
Re: sqlldr and To_date function not working [message #255990 is a reply to message #255989] Thu, 02 August 2007 12:43 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
http://www.orafaq.com/forum/m/253790/105911/?srch=SQLLDR#msg_253790
Re: sqlldr and To_date function not working [message #255991 is a reply to message #255989] Thu, 02 August 2007 12:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
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
Re: sqlldr and To_date function not working [message #255997 is a reply to message #255991] Thu, 02 August 2007 12:55 Go to previous message
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
Previous Topic: Why did the dump file extracted too large?
Next Topic: FNDLOAD is not loading non-mandatory parameters
Goto Forum:
  


Current Time: Wed Dec 07 11:03:45 CST 2016

Total time taken to generate the page: 0.14238 seconds