Home » RDBMS Server » Server Utilities » SQLLoader date formatting help (Oracle 10g)
SQLLoader date formatting help [message #417262] Thu, 06 August 2009 16:53 Go to next message
jimiki
Messages: 4
Registered: August 2009
Junior Member
I am trying to sqlload a file from sybase.
The bcped file looks like this (pipe delimited).

Test data:
BROKER |A00520 | |0|000000000912 | | |0|Jan 1 1753 12:00:00:000AM|ASSOCIATION COMPANY|1|Sep 11 2006 11:49:33:246AM|Jan 1 1753 12:00:00:000AM

My control file looks as shown below.
LOAD DATA
INFILE 'ER_EXTE.dat'
BADFILE 'EXTE.err'
APPEND
INTO TABLE ER_EXTE
FIELDS TERMINATED BY "|"
( EXTU_CONSTITUENT,
EXTE_ID,
GRGR_ID,
MEME_SFX INTEGER,
EXTU_ID ,
MEME_REL ,
MEME_ID_NAME ,
MEME_CK INTEGER ,
MEME_BIRTH_DT DATE "Month DD YYYY",
EXTE_NAME ,
EXTE_STS ,
EXTE_CREATE_DTM DATE "Month DD YYYY" ,
EXTE_PROCESS_DTM DATE "Month DD YYYY"
)

When I run sqlloader it says invalid month.
sqlldr userid=test/test control=TEST.ctl log=TEST.log

Being new to oracle, can anyone help how the control file should look like to load the file. Thank you

Report message to a moderator

Re: SQLLoader date formatting help [message #417263 is a reply to message #417262] Thu, 06 August 2009 17:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/part_ldr.htm#i436326

http://www.orafaq.com/wiki/SQL*Loader_FAQ

The date mask must match data in the file.

[Updated on: Thu, 06 August 2009 18:25]

Report message to a moderator

Re: SQLLoader date formatting help [message #417264 is a reply to message #417262] Thu, 06 August 2009 18:44 Go to previous messageGo to next message
jimiki
Messages: 4
Registered: August 2009
Junior Member
I know the date mask is not right.

I tried several and it didn't work.

Wondering whats the correct format for this.

Some examples I tried are
MEME_BIRTH_DT "to_timestamp(:MEME_BIRTH_DT,'MON dd yyyy HH:MI:SS.ff3AM')"

MEME_BIRTH_DT "to_timestamp(:MEME_BIRTH_DT,'Mon dd yyyy hh:mi:ss.ff3AM')"

Report message to a moderator

Re: SQLLoader date formatting help [message #417265 is a reply to message #417262] Thu, 06 August 2009 18:45 Go to previous messageGo to next message
jimiki
Messages: 4
Registered: August 2009
Junior Member
My data looks as below
Nov 15 2006  2:42:57:980PM

Report message to a moderator

Re: SQLLoader date formatting help [message #417266 is a reply to message #417262] Thu, 06 August 2009 18:46 Go to previous messageGo to next message
jimiki
Messages: 4
Registered: August 2009
Junior Member
CNTL file tried is as follows:
LOAD DATA
INFILE 'ER_EXTE.dat'
BADFILE 'EXTE.err'
APPEND
INTO TABLE ER_TB_SYST_EXTE_EUSER3
FIELDS TERMINATED BY "|"
(  EXTU_CONSTITUENT,
   EXTE_ID,
   GRGR_ID,
   MEME_SFX INTEGER,
   EXTU_ID         ,
   MEME_REL        ,
   MEME_ID_NAME    ,
   MEME_CK INTEGER  ,
   MEME_BIRTH_DT "to_timestamp(:MEME_BIRTH_DT,'MON dd yyyy HH:MI:SS.ff3AM')",
   EXTE_NAME       ,
   EXTE_STS        ,
   EXTE_CREATE_DTM "to_timestamp(:EXTE_CREATE_DTM,'MON dd yyyy HH:MI:SS.ff3AM')" ,
   EXTE_PROCESS_DTM "to_timestamp(:EXTE_PROCESS_DTM,'MON dd yyyy HH:MI:SS.ff3AM')"
)

Report message to a moderator

Re: SQLLoader date formatting help [message #417270 is a reply to message #417262] Thu, 06 August 2009 19:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/?SQ=37de010620a8c97810b725f031de4040&t=search&srch=timestamp+&btn_submit=Search&field=all &forum_limiter=10&search_logic=AND&sort_order=DESC&author=

Report message to a moderator

Re: SQLLoader date formatting help [message #417387 is a reply to message #417266] Fri, 07 August 2009 15:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
Senior Member
The following table in the searchable online documentation lists the datetime format elements. Notice, for example, that "Month" is the full name of the month, like "August", while "Mon" is the three-letter abreviation for the month, like "Aug".

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements004.htm#CDEHIFJA

Report message to a moderator

Re: SQLLoader date formatting help [message #417393 is a reply to message #417262] Fri, 07 August 2009 15:58 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
jimiki wrote on Thu, 06 August 2009 17:53

Test data:
BROKER |A00520 | |0|000000000912 | | |0|Jan 1 1753 12:00:00:000AM|ASSOCIATION COMPANY|1|Sep 11 2006 11:49:33:246AM|Jan 1 1753 12:00:00:000AM



Your data and your format mask for milliseconds is out of whack. Either use a colon in the format mask or a period in the data.

Report message to a moderator

Previous Topic: Load mutiple tables using SQL loader
Next Topic: Problem in loading data for clob columns
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Jan 11 09:24:33 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.