Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SSIS and dealing with embedded double quotes Expand / Collapse
Author
Message
Posted Friday, January 16, 2009 3:35 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 24, 2013 5:00 PM
Points: 50, 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?
Post #638571
Posted Friday, January 16, 2009 4:25 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 8:44 AM
Points: 1,073, Visits: 6,334
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]

Post #638583
Posted Tuesday, November 24, 2009 9:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:21 AM
Points: 21, Visits: 46
<retracted>
Post #823977
Posted Tuesday, November 24, 2009 9:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 5,259, Visits: 12,195
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #823995
Posted Tuesday, March 8, 2011 9:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:30 PM
Points: 102, Visits: 485
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?
Post #1075293
Posted Wednesday, March 9, 2011 12:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 5,259, Visits: 12,195
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1075356
Posted Wednesday, March 9, 2011 2:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 13,570, Visits: 11,383
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1075401
Posted Wednesday, March 9, 2011 3:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 5,259, Visits: 12,195
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1075403
Posted Wednesday, March 9, 2011 3:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 13,570, Visits: 11,383
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1075409
Posted Wednesday, March 9, 2011 3:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 5,259, Visits: 12,195
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?



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

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1075414
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse