Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Unanswered: SQL Laoder question

    Hello,


    I am in a bit of a dilemma.

    I am attempting to load a table from a file that is for the most part is fixed length. However, the last field in the file is an email address and ends wherever the last character of the email address ends.
    For example:
    .ctl file
    --------------------------------------------------------------
    LOAD DATA
    PRESERVE BLANKS
    INTO table employee
    (
    employee_no position(5:13),
    pay_group_ind position(1:1),
    company_cd position(2:3),
    union_ind position(4:4),
    first_name position(14:28) "rtrim(:first_name, ' ')",
    middle_name position(29:43) "rtrim(:middle_name, ' ')",
    last_name position(44:68) "rtrim(:las_name, ' ')",
    suffix position(69:71) "rtrim(:suffix, ' ')",
    gender position(72:72),
    address_1 position(73:102) "rtrim(:address_1, ' ')",
    address_2 position(103:132) "rtrim(:address_2, ' ')",
    city position(133:160) "rtrim(:city, ' ')",
    state position(161:162) "rtrim(:state, ' ')",
    zip_code position(163:167),
    zip_ext position(168:171),
    status_cd position(172:172),
    territory_no position(173:178) "rtrim(:territory_no, ' ')",
    email_address position(179:238) "rtrim(:email_address, ' ')"
    )
    ------------------------------------------------------------

    The last field is variable length. The file coming in is NOT comma delimited. Will the above code work or is there something I can use to specify that after position 179 to use the remaining bytes as the last field?


    Thanks in advance for your help!

    JAR

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    have you tried without the position statement for the last column only?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jan 2004
    Posts
    5
    nevermind.

    After testing with correct data it seems that SQL loader is able to detect the end of the line and does not read bytes from the next record if the first record is short. Thanks anyway....

Posting Permissions

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