Home » RDBMS Server » Server Utilities » sql loader control file help (10.1.0.1 unix)
sql loader control file help [message #554687] Wed, 16 May 2012 09:33 Go to next message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
Hi

I have a below example of data which is comma seperated, the first column is date and time which has been seperated by comma which is ideally a single columna and i have to load them in single column.

05/16/12,13:15:51,resn-j36-ctc119,"USR:ERM PMACTION END CONV ALIAS ASSIGN",0.480,P,pmoffice,14580,18188,475832935039,RXQBHT20431,192.168.97.146,0,"FinishAddEncounterAction:E",2,"200","",""

05/16/12,13:15:55,resn-j36-ctc119,"USR:ERM PMACTION DATA COMMIT",4.933,P,pmoffice,14580,18188,475832935039,RXQBHT20431,192.168.97.146,0,"200",0,"117106","237",""

my Control file is below

load data
BADFILE '/backup/temp/rajesh/CERNER/BadFiles/FILENAME'
append into table TEMP_CERNER_RESP_TIME_LND
TRAILING NULLCOLS
(
TRANSACTION_END_TIME TIMESTAMP
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' "TO_TIMESTAMP(:TRANSACTION_START_TIME,'DD/MM/YYYY HH24:MI:SSXFF')",
INSTALLATION_ID CHAR
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' substr(FILENAME,1,INSTR(FILE_NAME,'_',1,1) -1),
'AUTO-'||To_Char(prt_trs_id_seq.nextval),
TRANSACTION_ID CHAR
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
SERVER_ID CHAR
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
CLINICAL_TRANSACTION_ID CHAR
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
RESPONSE_TIME DECIMAL EXTERNAL
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
LOCATION_ID CHAR
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
WAIT_TIME DECIMAL EXTERNAL
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
INTERNAL_TRANSACTION_ID CHAR
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
INTERNAL_TIME DECIMAL EXTERNAL
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
EXTERNAL_SERVICE_ID CHAR
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
EXTERNAL_SERVICE_TIME DECIMAL EXTERNAL
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
)

My troule is to load 05/16/12,13:15:51 into single column as 05/16/12 13:15:51. Please help me as the ',' is used as a feild seperation and data and time is also seperated by comma
Re: sql loader control file help [message #554689 is a reply to message #554687] Wed, 16 May 2012 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: sql loader control file help [message #554691 is a reply to message #554689] Wed, 16 May 2012 10:01 Go to previous messageGo to next message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
Hi

If you pointing out the spelling mistake then i am amending the last line.

My trouble is to load 05/16/12,13:15:51 into single column as 05/16/12 13:15:51. Please help me as the ',' is used as a feild seperation and data and time is also seperated by comma.

Please let me know you concern about my post
Re: sql loader control file help [message #554696 is a reply to message #554691] Wed, 16 May 2012 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How Oracle can know if it is not a field separator but part of the data when you told it is a field separator?
I see 3 ways:
- enclosed the data between " (as you told Oracle)
- change the value in the file before loading it (a simple sed statement)
- use fix length syntax for the first field (if it actually is of fix length)

Regards
Michel
Re: sql loader control file help [message #554699 is a reply to message #554696] Wed, 16 May 2012 10:52 Go to previous messageGo to next message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
Many thanks
Michel,

3 option suit me if i can do both fix lenght and comma seperated.
first and second is difficult as the data volume is around 7-8 millions every day
Re: sql loader control file help [message #554702 is a reply to message #554699] Wed, 16 May 2012 11:05 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, you can use both positional and delimited field declarations. See this Barbara's message.
Re: sql loader control file help [message #554704 is a reply to message #554702] Wed, 16 May 2012 11:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Another option is to declare the first two comma-separated values as boundfillers, then concatenate them together as a calculated column. Since you did not provide your table structure, I have provided a simplified demonstration below, loading just the first column. Note that the boundfillers must be in the order they are encountered in the data file and the calculated column must be at the end after all other fields.

-- test.dat:
05/16/12,13:15:51,resn-j36-ctc119,"USR:ERM PMACTION END CONV ALIAS ASSIGN",0.480,P,pmoffice,14580,18188,475832935039,RXQBHT20431,192.168.97.146,0,"FinishAddEncounterAction:E",2,"200","",""
05/16/12,13:15:55,resn-j36-ctc119,"USR:ERM PMACTION DATA COMMIT",4.933,P,pmoffice,14580,18188,475832935039,RXQBHT20431,192.168.97.146,0,"200",0,"117106","237",""


-- test.ctl:
load data
INFILE test.dat
append into table TEMP_CERNER_RESP_TIME_LND
TRAILING NULLCOLS
(filler1 BOUNDFILLER TERMINATED BY ',',
filler2 BOUNDFILLER TERMINATED BY ',',
column1 "TO_TIMESTAMP (:filler1 || ' ' || :filler2, 'MM/DD/YYYY HH24:MI:SS')"
)


-- table, load, and results:
SCOTT@orcl_11gR2> CREATE TABLE TEMP_CERNER_RESP_TIME_LND
  2    (column1  TIMESTAMP)
  3  /

Table created.

SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SCOTT@orcl_11gR2> SELECT * FROM temp_cerner_resp_time_lnd
  2  /

COLUMN1
---------------------------------------------------------------------------
16-MAY-12 01.15.51.000000 PM
16-MAY-12 01.15.55.000000 PM

2 rows selected.






Re: sql loader control file help [message #554706 is a reply to message #554704] Wed, 16 May 2012 11:53 Go to previous message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
Wonderfull solution Barbara.

Thanks a lot barbara.
Previous Topic: oracle metalink
Next Topic: sql loader syntax error 350
Goto Forum:
  


Current Time: Thu Mar 28 04:24:05 CDT 2024