SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS and dealing with embedded double quotes


SSIS and dealing with embedded double quotes

Author
Message
mrtrich99
mrtrich99
Old Hand
Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)

Group: General Forum Members
Points: 350 Visits: 161
Is there a way for SSIS to overlook embedded double quotes in the data so when I run my package and the "for each loop" gets executed and imports all the files in a folder into a table the columns wont shift because of the embedded double quotes?

I've read about the Undouble add-on that you download from the microsoft website but I have not had any luck with the installation so I couldn't test to see if it would work. I also tried using OPENROWSET command but anything after the column with a double quote would be NULL.

So basically, here's an example from the csv I'm working with:
"ID","Name","City","Age","Title"
1,"John "The Man, II" Doe","Gotham",30,"Villian"

There are no double quotes around 1 and 30 because those are numeric fields when they were extracted into a csv file. The city, age, and title are shift one or two columns over and the name would only be "John".

Does anyone have any ideas on a work around for this?
Todd Engen
Todd Engen
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2739 Visits: 6336
Unfortunately SSIS in SQL 2005 and 2008 does not support embedded text qualifiers in CSV files.

Vote on it at Microsoft Connect.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=312164

One workaround is to read in a single line at a time, then parse each line in a Script component before going to the destination.

[Source] -> [Script Component] -> [Destination]


jpratt-797544
jpratt-797544
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 79
<retracted>
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50375 Visits: 21152
Yuk, nasty. Easiest solution: change the field delimiter character to something else in the source data.

Otherwise (and this may not work perfectly), you could consider running a replace routine on the source data before reading it using SSIS. Replace ," with ,| (or whatever your new delimiter is) and replace ", with |, and then use | as your delimiter.

I said that it may not work perfectly because
1) There may be some additional spaces in there - but you could code around that, and
2) Your embedded text could conceivably contain this combination of characters too.

Phil


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
nawillia
nawillia
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 554
Hello
i am running into a similar problem with the embedded double quotes.
I tried using the redirect row on error to just skip those rows with the double quotes.

I have not gotten it to work. Does anyone know of a way to just skip over that row and keep processing?
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50375 Visits: 21152
Other than the ideas put forward by myself and Todd, I know of no way forward for you.

There is nowhere in the system where you can set a flag: "Skip rows that cannot be parsed" or whatever.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61007 Visits: 13297
natarshia.peck (3/8/2011)
Hello
i am running into a similar problem with the embedded double quotes.
I tried using the redirect row on error to just skip those rows with the double quotes.

I have not gotten it to work. Does anyone know of a way to just skip over that row and keep processing?


How exactly is this causing troubles?
For testing purposes, I loaded a flat file with embedded double quotes into SSIS 2008 and it imports just fine.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50375 Visits: 21152
Koen Verbeeck (3/9/2011)
natarshia.peck (3/8/2011)
Hello
i am running into a similar problem with the embedded double quotes.
I tried using the redirect row on error to just skip those rows with the double quotes.

I have not gotten it to work. Does anyone know of a way to just skip over that row and keep processing?


How exactly is this causing troubles?
For testing purposes, I loaded a flat file with embedded double quotes into SSIS 2008 and it imports just fine.


Really - even though you specified " as the text-field delimiter?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61007 Visits: 13297
Phil Parkin (3/9/2011)
Koen Verbeeck (3/9/2011)
natarshia.peck (3/8/2011)
Hello
i am running into a similar problem with the embedded double quotes.
I tried using the redirect row on error to just skip those rows with the double quotes.

I have not gotten it to work. Does anyone know of a way to just skip over that row and keep processing?


How exactly is this causing troubles?
For testing purposes, I loaded a flat file with embedded double quotes into SSIS 2008 and it imports just fine.


Really - even though you specified " as the text-field delimiter?


Yes. But then I did some more testing, and it starts to screw up when there are double quotes AND commas in one string.
Conclusion: use another delimiter for god's sake. The comma is one of the most used punctuation marks in strings. Use a pipe | as a delimiter, because who uses that symbol in a normal sentence? 99% of your problems will go away.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50375 Visits: 21152
That's a message I always try to get across. The fact that the files are usually called 'Comma Separated' is not helping the cause one bit and I think we have an uphill struggle.

Good work with the testing by the way. Not got much work to do today? :-D


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search