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  |
 |
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 #554704 is a reply to message #554702] |
Wed, 16 May 2012 11:16  |
 |
Barbara Boehmer
Messages: 9104
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Mar 03 16:13:27 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
