Post reply

Importing Text File problem

  • July 14, 2010 at 3:49 pm

    #142038

    I have a text file that I am having problems with importing.

    Since text is imported generally as a tab delimited file I am

    finding that if my last column PhoneNumber has no data

    then the first field of the next line goes in PhoneNumber

    and the table is all messed up.

    Example

    File (short version):

    OrderID ZipCode PhoneNumber

    1 99999 5198881414

    2 99999

    3 99999 5198886542

    Table:

    OrderID ZipCode PhoneNumber

    1 99999 5198881414

    2 99999 3

    99999 5198886542

    Any thoughts to a resolution? I don't even know where to begin.

    Thanks!

  • July 14, 2010 at 10:49 pm

    #1193235

    Check..... This will resolve ur problem.

    bcp [ServerName].[Owner].[TableName] in "Path for text file" -c -SServerName -UUserName -PPassword

  • July 15, 2010 at 12:45 am

    #1193266

    The general solution to this sort of problem is to import the entire row into a single big varchar field and then break it up into your required fields as part of the dataflow, using derived columns or a script component to do the split.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 15, 2010 at 7:37 am

    #1193480

    Could you give me an example of this?

    Thx.

  • July 15, 2010 at 8:55 am

    #1193556

    I just did a presentation for the Oklahoma City SQL Server User Group on how to use the script task to handle ragged or irregular files such as this:

    http://okccoco.blip.tv/file/3874415/

    hth,

    Tim

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply