Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Unanswered: Delimited Problem

    Hi.

    I have a data source file which I need to load into my SQL table using DTS package. | is used as delimited to separate each field. Recently, i discovered some of the fields came with | has part of the field content. This has caused some problem with the loading process because the field has been split into 2 because | is recognised as a new field.

    What can I do now? I can't change the delimited to something else or neither can I stop my data source from using | as this is beyond my control.

    Please advise. Thank you.


    Regards
    John Smith

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    Perhaps using a fmt-file that describes the input file is an option.

  3. #3
    Join Date
    Jan 2005
    Posts
    10
    why not open the file in Excel. WHen in excel, fix the column separator then load the file in DTS as XLS file as source.

  4. #4
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163
    You MUST chose another delimiter. Other way it's possible, but you must know the exact size of the fields and bu sure that the file use each filed size.

    Paulo

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are screwed unless you can find some pattern to the usage of the pipe delimiter in your data values. If you can find a reliable pattern then you can handle them by loading each record into a staging table as a single string, and then removing or replacing the offending character prior to populating your production tables.

    Can you give a few examples where a pipe delimiter exists in your data? Include both the field with the pipe character, as well as the field immediately prior to it, and the pipe delimiter between the fields as well.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71
    Hi, I have the same problem as John but mine was ~ instead of | bar. Can't do much too.

  7. #7
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163
    If you have isolated ~ you may use them as a field delimiter. Otherwise you are "in the bushes".

    Paulo

  8. #8
    Join Date
    Jan 2005
    Posts
    2
    Hey Guys.

    Sorry, couldn't thank you people earlier for your advises because I was down with a bad flu and only back to work today. I will see what I could do & perhaps change the delimiter.

    Thank you guys.

    Reagrds
    John Smith

Posting Permissions

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