Post reply

DTS - import from CSV problems - "comma space" delimited

  • November 1, 2006 at 2:22 pm

    #71044

    Hi all,

    I am having some problems correctly importing data from CSV files that have a space after each comma.  The CSV file has a mix of text and int fields, and the text fields are encapsulated with double-quotes (ie "Fred"). 

    A sample line would be:

    "Fred", "11-01-2006", 27, "Happy Birthday"

    When I try to import this with DTS, the text fields get hung-up on the spaces and I get { "Fred"} instead of {Fred} (brackets added to show space and quotation marks).

    Any ideas appreciated.  I am trying to get around importing everything in as varchars and cleaning the "s and spaces up after the DTS import.

    Does SSIS handle this better?

    Thanks,

    Rob

  • November 1, 2006 at 2:58 pm

    #669354

    You can try to change the Transformations in the data pump to use an ActiveX script.  You can then TRIM the first space off in the ActiveX script.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • November 2, 2006 at 6:33 am

    #669454

    If you're using SQL 2005, you may use CLR and have a Regular Expressions to parse the csv string.

    Assuming the csv record is less than 8K you may import the csv record as one string. Then with a CLR stored procedure break the records down.

    I use the following Regular Expression to parse my csv data in VB.Net.

    Dim aryValues() As String

    Const sPattern As String = ",(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"

    Dim rex As System.Text.RegularExpressions.Regex = _

                  New System.Text.RegularExpressions.Regex(sPattern)

    aryValues = rex.Split(psRecord)

    aryValues(x).Replace("""", "").Trim   'x is your array position

    ...remaining code to insert or update the table.

    I have not applied the code with SQL CLR but it's a thought.

  • November 2, 2006 at 6:53 am

    #669456

    On the csv source in you DTS package, be sure you have the Text Qualifier set to Double Quote ".  It sounds like it may be set to none.

    hth

  • November 2, 2006 at 8:32 am

    #669504

    I ran into this problem too. I opened the csv file in a text editor (free one is Notepad++) saved it as a .txt and was able to import the .txt file cleanly into SQL2005 using the import functions. 

  • November 2, 2006 at 3:23 pm

    #669638

    Hi John,

    Thanks for the suggestion!  That pointed me in the right direction.

    Actually the activex trim function of { "Fred"} made the string import as {"Fred"} (with quotes) into the SQL Server table - even though I specified that double quotes was the text qualifier.

    So I ended up doing this in the activex transformation:

     DTSDestination("name") = MID(DTSSource("Col001"),3,(len(DTSSource("Col001")) - 3))

    and it gave me what I needed (turned { "Fred"} into {Fred})

    Thank you all for the suggestions.

    Rob

  • November 2, 2006 at 4:47 pm

    #669658

    Have you checked the properties of your source text file in your DTS package?  I have seen the problem you encountered, and the fix was to set the text delimiter to double quotes.

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

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