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

Question

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

Rob-188054

SSC-Addicted

Points: 416
More actions
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

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

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

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

President of the U.S. Grasshopper Points: 13 More actions · Answer 2

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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

Lowry Kozlowski SSCommitted Points: 1918 More actions · Answer 4

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.

Rob-188054 SSC-Addicted Points: 416 More actions · Answer 5

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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.