Post reply

Space Delimited File Import with DTS

  • June 19, 2006 at 9:45 am

    #90230

    I have a set of large text files where the data is delimited by spaces. In some cases, there is a double quote around text that I need to import.

    Row Examples:

    somedata1 somemoredata1 "data between quotes1" evenmoredata1 "more double quotedata1"

    somedata2 somemoredata2 "data between quotes2" evenmoredata2 "more double quotedata2"

    ....

    somedataN somemoredataN "data between quotesN" evenmoredataN "more double quotedataN"

    When using the DTS Import/Export Wizard I attempt to set this up as a text file import. I'm having trouble specifying the file format.

    I attempt to set the row delimiter to {CF}{LF} and the text qualifier to Double Quote {"}. When I try to set the delimiter to Other and type in a space in the box I get the following error message:

    "DTS Import/Export Wizard Error"

    Error Description: Invalid delimiter data: text qualifier mest be followed by a column delimiter (except the last column)

    Context: Error calling OpenRowset on the provider"

    I have also try specifying space as { } and {SPACE} and {sp} and {space} but none of them work. I get the same error message.

    I'm guessing that the DTS wizard does not generate the proper code for this type of file I'm trying to import. If I choose another delimiter I do not get the error. But none of those options will work for me as I cannot modify the file structure. It must be imported in our database table as formatted.

    Any ideas? Is this DTS Import wizard the way to import this type of file or should I be considering using another solution?

    SQL Server Standard Ed. v8.00.2040 (SP4)

    Management Console 1.2

  • June 19, 2006 at 12:27 pm

    #644663

    suggestions - try using space(1) or char(32) and see if that works!







    **ASCII stupid question, get a stupid ANSI !!!**

  • June 20, 2006 at 2:02 am

    #644742

    I've just tried it in Management Studio and it seems to work fine. Just selected Flat File Source, set " as Text Qualifier, clicked on Columns, clicked the mouse in the column delimiter drop down list and typed a space, clicked the Refresh hyperlink above the data preview and it split it into the columns perfectly. I then ran the package and it imported the two sample rows I copied from your article. Did you miss clicking the Refresh link perhaps ?

    Dan
    www.firstcs.co.uk

  • February 12, 2007 at 2:58 am

    #689102

    somedata1 somemoredata1 data between quotes1 evenmoredata1 more double quotedata1

    somedata2 somemoredata2 data between quotes2 evenmoredata2 more double quotedata2

    hi,

    My .txt filr contains the above type of data.I have tried all most all the options.Please suggest me something.Its giving the same error.

    Regards,Aritri

  • January 11, 2008 at 5:29 am

    #767041

    just typing a space in "colums delimiter" worked for me.

  • January 11, 2008 at 9:38 pm

    #767386

    If there's nothing that violates privacy laws or company policy, post the first ten lines of data so we can resolve this correctly, please.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 8, 2010 at 8:24 am

    #1100927

    I'm trying to follow the procedure described here, but when I click on the Columns control on the left the field for Column delimiter is greyed-out preventing me from typing something there. Any ideas what could be causing this and what I should do to work solve this?

Viewing 7 posts - 1 through 6 (of 6 total)

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