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
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65152 Visits: 13298
Phil Parkin (3/9/2011)
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


Nope. I'm actually between projects for the moment, so I'm enhancing my SSIS skills by performing such little tests.


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 (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54124 Visits: 21211
Impressive commitment! When I am 'between projects' I tend to work on improving my guitar skillsSmooooth


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
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 554
Well a. i'm a new employee and the file we get are text qualified with "" and comma delimited. so a row looks like this
"some text","some text2","some name "BookTitle" to buy ", "end"


when the package sees a " it thinks that its the end of the field, and complains that it can't find the , after the "

I talked my supervisor about saying it isn't text qualified, but that would mean the "" would show up in our rows.

I guess based on all the responses we can ask for a different delimiter or for them not to include the "" in the title names, but i need some solution to load these things because a change is probably not going to happen super fast.

Right now all i can do is open the file and manually remove the lines or fix them.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54124 Visits: 21211
natarshia.peck (3/9/2011)
Well a. i'm a new employee and the file we get are text qualified with "" and comma delimited. so a row looks like this
"some text","some text2","some name "BookTitle" to buy ", "end"


when the package sees a " it thinks that its the end of the field, and complains that it can't find the , after the "

I talked my supervisor about saying it isn't text qualified, but that would mean the "" would show up in our rows.

I guess based on all the responses we can ask for a different delimiter or for them not to include the "" in the title names, but i need some solution to load these things because a change is probably not going to happen super fast.

Right now all i can do is open the file and manually remove the lines or fix them.


Your idea about importing as not text qualified has some merit. You could bring the data in 'as is' and then run some UPDATE commands afterwards that would tidy most of it up (by removing the first and last character of the offending fields, where field length > 2, perhaps).


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
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 554
Thanks. I have to move on this fast because I inherited this package and I have other packages to develop/test. So I need to report up what the deal is with the pack failing and some 'resolution'.

The thing that perplexes me most, and I just had another dev look at this, is why when the parse fails, the redirect on error doesn't just skip the row.

I guess like the post says, there is nothing in ssis that will do this.
Todd Engen
Todd Engen
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2903 Visits: 6336
One way to speed up the preprocessing of your CSV files is to use Log Parser to convert them to TSV files. A TSV should be easier for SSIS to make use of; Log Parser 2.2

For example, if input.csv contained this:

Author, Title,Quote
"Salinger","The Catcher in the Rye","""People always think something's all true."""
"Trumbo","Johnny Got His Gun", """S.O.S. Help me."""



This would change the delimiters to tabs and remove the double quotes.

LOGPARSER -i:CSV -o:TSV "SELECT Author, Title,Quote INTO output.tsv FROM input.csv"


nawillia
nawillia
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 554
thanks i am going to test that today
dg227
dg227
SSChasing Mays
SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)

Group: General Forum Members
Points: 613 Visits: 819
Building on the other suggestions and comments earlier in the thread, and if you're able to make file change requests, I'd recommend removing the text qualifier (so you don't get the possibility of the qualifier showing up in the middle of the text, bombing out the import like you've seen), but instead of just changing the field delimiter from a comma to some other single character (pipe, etc.), consider a double-character field delimiter.

I implemented this as a fix for some vendor data a few years back after your same issue came up with them, switched to no text qualifiers and a double-character field delimiter (we're using a tilda-carat: ~^ as a delimiter), and haven't had any issues since. It'll make the files look ugly, but it works.

Removing text qualifiers and only switching the field delimiter to another character still leaves open the chance that bad data with that new delimiter character can show up and mess with your import (I never believe the response, "That character will never be sent in our data." ... it'll happen eventually). A double-character delimiter reduces the chance that a data field will ever contain that combo to almost nothing.

Just a thought ... but bad data is still bad data if it's not sent to you in your established/spec format.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65152 Visits: 13298
dg227 (3/10/2011)

Just a thought ... but bad data is still bad data if it's not sent to you in your established/spec format.


And you know what they say:
crap in is crap out :-D


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
dg227
dg227
SSChasing Mays
SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)

Group: General Forum Members
Points: 613 Visits: 819
Koen Verbeeck (3/10/2011)
dg227 (3/10/2011)

Just a thought ... but bad data is still bad data if it's not sent to you in your established/spec format.


And you know what they say:
crap in is crap out :-D


So true ...
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