Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    7

    Unanswered: Oracle Sql loader load fail

    Hi
    . I am trying to Load records from a text file to a oracle table using SQL Loader. i have a text file that is a tab delimited and all the fields are enclosed by double quotes( " " ). i am trying to read all the columns in the record.
    but there are some records that have one or the other columns missing.there is no pattern on which column is missing. so that record is rejected or it will load incorrectly. is there a way in sql loader to specify to load a default or null value when it encounters a blank. Remember this is a tab delimeted column record.
    Here are some rejected records.
    record format(Fname Lname, address1, address2, city,state,zip,areacode, phone num)
    In this case addr1, address2, Zip field are missing:
    "FGHFDHGFDH" "DHZ" "HOBOKEN" "NJ" "201" "2161234"
    Here address1, address2,ZIP and STATE are missing:
    "SHANNON" "DELINA" "STEELE" "989" "6542286"
    "SHANNON" "JAMES" "STEELE" "989" "6542286"

    there are tabs between all the columns..
    Any help would be greatly appreciated
    thx
    babu

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    Is there still a tab for the columns it skips??

    so, if it skips 3 columns there would be 3 TABS and then data?

    ie: "tr"<tab><tab><tab>"eb"
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Aug 2003
    Posts
    7
    Originally posted by The_Duck
    Is there still a tab for the columns it skips??

    so, if it skips 3 columns there would be 3 TABS and then data?

    ie: "tr"<tab><tab><tab>"eb"
    yes thats true. also it can be
    "tr" <tab><tab>"eb"<tab><tab>

  4. #4
    Join Date
    Jul 2003
    Posts
    11
    Try to use
    TRAILING NULLCOLS

  5. #5
    Join Date
    Aug 2003
    Posts
    7
    Originally posted by Harsh
    Try to use
    TRAILING NULLCOLS

    i tried that before but no luck. the reason is that there is no specific loaction that the column is missing. in one record the fifth column is missing and in another record second column is missing.......

  6. #6
    Join Date
    Aug 2003
    Posts
    7
    Originally posted by Harsh
    Try to use
    TRAILING NULLCOLS

    i tried that before but no luck. the reason is that there is no specific loaction that the column is missing. in one record the fifth column is missing and in another record second column is missing.......

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    you never answered my question.

    use a utility where you can see VISIBLE SPACES to determine if the TABS are in there or not.

    If they are in there you are golden.

    If they are not in there, tell whoever gave you that file to take a flying leap.

    Unless you have FIXED-WIDTH or TABS it is impossible.
    By Impossible I mean that you would spend SO MUCH time debugging the stupid data that it would not be worth it.

    example: what you are doing now because of that stupid file (not your fault, but you unfortunately have to deal with the stupid file).
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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