Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Location
    new york
    Posts
    2

    Unanswered: SQL Loader single record in multiple rows

    I have data with 70 columns. For some records in this a unix line termination $ appears before the 70 columns - just bad data.

    So when i try loading the data using the control file
    LOAD DATA
    INFILE 'filename.dat'
    BADFILE 'filename.bad'
    DISCARDFILE 'filename.dsc'

    APPEND
    INTO TABLE "TABLE_A"
    APPEND

    FIELDS TERMINATED BY '~'
    TRAILING NULLCOLS

    (COL_1,
    COL_2,
    ..........

    COL_70
    )

    The sql*loader treats the rows with premature $ as separate records. How can i make the records spread over multiple rows be treated correctly as one record by sql loader?

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    replace '$' with ''??
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Aug 2003
    Location
    new york
    Posts
    2
    2 questions :
    1.tr -d '\004' < filename.dat > newfile.dat does not remove the end-of-transmission character and removing \044 - ascii for $ symbol doesn't help as this is end-of-trnsmission - so how does one remove/replace this
    2.the valid end-of-trnsmission at the end of a propoer record - in which case all the 70 fields are together before the $ will also be removed -- will this not cause a confusion. How would sqlloader know the end of line?

    Thank You in advance
    Shanky

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Exclamation

    Originally posted by tshanky19
    -- will this not cause a confusion. How would sqlloader know the end of line?
    This is exactly the opposite of your problem!
    How would sqlloader know it is a continued record?

    Unless... you could actually identify the continued record with some 'character' sequence and the use the 'CONTINUE IF' option.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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