 SQL*Load - prevent carriage return char in last column? [message #246624] |
Thu, 21 June 2007 08:56  |
stanjacko50
Messages: 6
Registered: June 2007
Location: London
|
Junior Member |
|
|
Hi,
I have written a SQL*Load control file to import data from a tab delimited file, but the last field that is loaded is not ended with a tab. The last field gets loaded with an extra char (carriage return) as follows: last column of interface file (TRANSACTION TYPE NAME) contains text "New Business" yet what gets loaded into the table is "New Business|" because there is no comma at the end of the field - I would like to fix this without having to change the file generation program.
Control file:
===============================================
OPTIONS ( SKIP=1)
LOAD DATA
INFILE *
APPEND
INTO TABLE "LV_AR_BROKER_INTERFACE"
APPEND
FIELDS TERMINATED BY X'9'
TRAILING NULLCOLS
(TRANSACTED_ID
,
TRANSACTED_DATE DATE "DD/MM/RRRR HH24:MI"
,
RATER_ID
,
TRANSACTION_TYPE_ID INTEGER
,
TRANSACTION_TYPE_NAME)
===============================================
Can anyone help please?
Thanks
[Updated on: Thu, 21 June 2007 09:10] Report message to a moderator |
|
|
|
|
|
|
|
| Re: SQL*Load - prevent carriage return char in last column? [message #246676 is a reply to message #246655] |
Thu, 21 June 2007 11:16  |
stanjacko50
Messages: 6
Registered: June 2007
Location: London
|
Junior Member |
|
|
Solved my problem by adding the rtrim to the last field as you can see below - thanks for your help
===============================================
OPTIONS ( SKIP=1)
LOAD DATA
INFILE *
APPEND
INTO TABLE "LV_AR_BROKER_INTERFACE"
APPEND
FIELDS TERMINATED BY X'9'
TRAILING NULLCOLS
(TRANSACTED_ID
,
TRANSACTED_DATE DATE "DD/MM/RRRR HH24:MI"
,
RATER_ID
,
TRANSACTION_TYPE_ID INTEGER
,
TRANSACTION_TYPE_NAME "rtrim(:TRANSACTION_TYPE_NAME,chr(13))"
)
===============================================
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
