Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108

    Unanswered: control file (trim whitespace/blank)

    hie all,
    im using sql loader to load data into my table but then i noticed that one column is being uploaded with blanks in the front of the data

    i will actually need to select this way and there is about 7 blank spaces in front...
    does anyone know how to remove this while loading ??
    (i have tried many ways but its still the same)

    select * from trap_ra_ers_dist
    where distributor_id = ' D1202-S1202'


    this is how my control file looks like(in attachment)
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Yes you can modify data while loading. In this case use e.g. trim(). I think your control file could looks like:
    Code:
    LOAD DATA
    --INFILE '/app/ITRAKPI/TRAP/TRAP_RA_ERS_DIST_${YYYYMMDD}.txt'
    APPEND INTO TABLE trap_ra_ers_dist
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (
    DATE_RECONCILATION  position(01:08)   "to_date((:date_reconcilation), 'yyyymmdd')",
    WEEK_NO             position(15)   "trim(:WEEK_NO)",
    DISTRIBUTOR_ID      position(36:55)   ,
    ORDER_NO            position(56:65)   ,
    TRANS_ID            position(66:79)   ,
    CURRENT_BALANCE     position(80:91)   ,
    AMOUNT_UPLOADED     position(92:103)  ,
    AMOUNT_PURCHASED    position(104:115) ,
    DISCOUNT            position(116:127) ,
    ORDER_TYPE          position(139:157) ,
    ORDER_STATUS        position(174:177) ,
    DATE_PURCHASED      position(178:185) "to_date((:date_purchased), 'yyyymmdd')",
    DATE_UPLOADED       position(192:199) "to_date((:date_uploaded), 'yyyymmdd')",
    DATE_MOD                              "(SELECT trunc(sysdate) FROM dual)"
    )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •