SQL*Loader - Tab Character Issue...

[wellster34]

Hello,

I have a data file like below:

Currency Code |Currency Description |
CAD |Canadian Dollars |
USD |US Dollars |

I have the option to skip=1 to ignore the header record and I have it terminated by |.

The issue is the tab character after the value. Is there a way to remove it? I know I can perform a RTRIM but that is for whitespsace not tab characters. Is there a DECODE logic I could use?

Here's my SQL*Loader control file:

OPTIONS (DIRECT=FALSE,SKIP=1)
LOAD DATA
INFILE '$download/$file_name.dat'
BADFILE '$bad_data/$file_name.bad'
DISCARDFILE '$bad_data/$file_name.dsc'
TRUNCATE
INTO TABLE IO.CURRENCY_REF
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
-- Identified Columns --
( CURRENCY_ID "RTRIMCURRENCY_ID)"
, CURRENCY_DESC_TX "RTRIMCURRENCY_DESC_TX)"
)


Thanks for your time

 

[SantaMufasa]

Wellster,

I am not in a position to test my suggestion out, so please test and advise the results. Here is my suggestion:

OPTIONS (DIRECT=FALSE,SKIP=1)
LOAD DATA
INFILE '$download/$file_name.dat'
BADFILE '$bad_data/$file_name.bad'
DISCARDFILE '$bad_data/$file_name.dsc'
TRUNCATE
INTO TABLE IO.CURRENCY_REF
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
-- Identified Columns --
  (   CURRENCY_ID           "RTRIM(:CURRENCY_ID[b],chr(9)[/b])"
  ,   CURRENCY_DESC_TX      "RTRIM(:CURRENCY_DESC_TX[b],chr(9)[/b])"
  )

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:42 (27Sep04) UTC (aka "GMT" and "Zulu"), 13:42 (27Sep04) Mountain Time)