Click here to monitor SSC
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
nawillia
nawillia
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 554
Thanks to all for responding so fast. I talked to the supplier of the file and they made the file adjustments with no problem :-D

Like I said earlier, I just started a few weeks ago, so i'm learning who to contact for what and their expectations -- some people think IT can do anything with everything.

But I like the idea of the double delimiter - i'm going to def put that in my toolbox of ideas to throw out when we start the next file import.
sonalijain.engg
sonalijain.engg
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1
while loading CSV with double quotes and comma there is one limitation that extra double quotes has been added and the data also enclosed with the double quotes you

can check in the preview of source file.
So, add the derived column task and give the below expression:-

(REPLACE(REPLACE(**RIGHT(SUBSTRING(TRIM(COL2),1,LEN(COL2) - 1),LEN(COL2) - 2)**," ","@"),"\"\"","\""),"@"," ")

the bold part removes the data enclosed with double quotes.

Try this and do let me know if this is helpful
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16461 Visits: 13207
sonalijain.engg (3/26/2013)
while loading CSV with double quotes and comma there is one limitation that extra double quotes has been added and the data also enclosed with the double quotes you

can check in the preview of source file.
So, add the derived column task and give the below expression:-

(REPLACE(REPLACE(**RIGHT(SUBSTRING(TRIM(COL2),1,LEN(COL2) - 1),LEN(COL2) - 2)**," ","@"),"\"\"","\""),"@"," ")

the bold part removes the data enclosed with double quotes.

Try this and do let me know if this is helpful


Useful suggestion, but I do hope the OP solved his problem 2 years ago ;-)



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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
sonalijain.engg
sonalijain.engg
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1
The soln provided by me is given in the package some people still stuck in such situation as i stucked so i thoght to gv this suggestion n plz dont focus on whether issue is resolved or not focus on the alternate approachCool
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8375 Visits: 19500
sonalijain.engg (3/27/2013)
The soln provided by me is given in the package some people still stuck in such situation as i stucked so i thoght to gv this suggestion n plz dont focus on whether issue is resolved or not focus on the alternate approachCool


This is a forum, not Wikipedia. People post problems and others help them solve them.

So instead of being unpleasant, why don't you focus on helping people who currently have problems?


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.
mrtrich99
mrtrich99
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 161
Useful suggestion, but I do hope the OP solved his problem 2 years ago ;-)


just like most people suggested, i now request for pipe delimiters, but sometimes I do still get files that are comma delimiter and the user extracting the data doesn't know how to change the delimiter. in those cases I have no choice but to suck it up and clean the data up manually.
Snargables
Snargables
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 709
running into this same issue. I read that 2012 could handle column dilimiters embedded in a text however It's not working for me. When i preview the data it woks fine however fails when trying to bring down the file. Also, I'm consuming the files and they are being generated by an ad server. So i cant specify what the delimiter will be. Not exactly sure how to proceed other than to use TALEND. I cant remove them from the data because its the "name" column and if it has double quotes in the name then so be it. This is really starting to make me angry.

If i end up having to convert this ssis to talend then i'm going to use talend going forward w/ the intent of moving away from ssis totally.
Snargables
Snargables
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 709
i suppose i can write an exe to search for the data issue and replace the double quote w/ two double quotes. its a band aid though
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8375 Visits: 19500
mrtrich99 (3/27/2013)
Useful suggestion, but I do hope the OP solved his problem 2 years ago ;-)


just like most people suggested, i now request for pipe delimiters, but sometimes I do still get files that are comma delimiter and the user extracting the data doesn't know how to change the delimiter. in those cases I have no choice but to suck it up and clean the data up manually.


Can you provide some sample failing data and a copy of the error message?


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-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 554
Wow, a blast from the past. Although this issue continues to plague me and data imports.
Now, for a different client, a developer coworker wrote up an ssis with script tasks, that if the file import fails it goes to another directory, so my other files load. Then for any files that fail, since they are csv, we open them up in excel and look for the double quotes and just manually remove them or do a find and replace. It's not fully automated but since only about 1 in 100 files fail, its a systematic process.
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