Re: SQLLDR

Brit.Willoughby_at_WichitaKS.ATTGIS.Com (Brit Willoughby) wrote:
>
> Lets say I have the following load.ctl file:
>
>load data
>infile 'MyData.DAT'
>append into table MyTable
>fields termilated by ',' optionally enclosed in '"'
>(Field1,Field2,Field3)
>
> The Field widths are 10,10,10, respectivly of char.
>
> The data looks like:
>
>"Some Data","This Data2 is too long!","Some Data"
>"My Data","This Data is too long too!","Data"
>"Data","Ok Data","Nice Data"
>
> How can I truncate the 2ed piece of data to ensure that is is only
>10 long and will fit in Field2?
>
> I can't figure out how to use the Position statement because the
>positions vary from line to line.
>

Brit,

I've not tried this in a comma/quote delimited file, but it works in a fixed format file:

(
  field1 position(x:x) char
 ,field2 char
  "SUBSTR(:field2,1,10)"
 ,field3 position(x:x) char
)

I've used DECODE's and INSTR's and SUBSTR's and LENGTH, etc., and it's worked well.

Let me know if you get it to work for you in free format.

-Mark

-- 
***********************************************************************
* Pickwick Software Co., Portland, Oregon (USA), ORACLE Specialists   *
* Mark Fontenot (mark_at_pickwick.com) & Ally McEvoy (ally_at_pickwick.com) *
***********************************************************************