CODEWITHSUNDEEP
sql-servercsvimportssis
Brisingr
Brisingr

Reputation: 82

SSIS Text Qualifier not working correctly

I have a CSV file I am importing through SSIS.Below is an sample of the data in my file

"MEM1001","OTHER","P" ,20101001,20781231,,20781231,20101001, "Medic","General >21" , "A100100" ,"2210",20101001,20781231

I have added , as column delimiter and " as Text Qualifier in the connection manager.

But columns like "P" ,"Medic","General >21" ,"A100100" , are still coming enclosed with double quotes when I preview the data while rest the of the string columns are coming without double quotes. I am guessing it has something to do with the spaces after the quotes.

Can somebody explain why this is happening and how can i make this columns to come without double quotes while importing the data from file to table.

Upvotes: 2

Views: 4775

Answers (3)

WanderingEarthMagnet
WanderingEarthMagnet

Reputation: 11

Why not just go to the Connection Manager for that csv file, click on Columns, and under the Column delimiter box just enter a space followed by a comma? Worked for me.

Upvotes: 1

Christoph S
Christoph S

Reputation: 773

I just stumbled across this post, I had the same issues, I was trying around and could not find any other solution.

The text qualifier " only works in csv files, when the quote is directly after the colon, no space after the colon and the text identifier/qualifier. I have no idea why.

If you aren't able to fix the input data, an option would be to create a derived column and to replace the double quotes. This worked for me: How to replace double quotes in derived column transformation?

Trim(REPLACE(COLA, "\"", ""))

You should also add the Trim(), otherwise you have empty spaces before and maybe after the word. This could be problematic in a merge join (in my case it was).

Upvotes: 1

user2081175
user2081175

Reputation:

I don't know why this extra spaces cause this issue.

Here is what I would do. It may not be the best idea, but it should work.

You will need to add script task before data flow task that would replace all " ," and ", " to ",".

Thank you

Upvotes: 0

Related Questions