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 Thursday, March 10, 2011 12:26 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 12:30 PM
Points: 102, Visits: 485
Thanks to all for responding so fast. I talked to the supplier of the file and they made the file adjustments with no problem

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.
Post #1076511
Posted Tuesday, March 26, 2013 12:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 12:17 PM
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
Post #1435652
Posted Wednesday, March 27, 2013 1:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 13,562, Visits: 11,371
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1435784
Posted Wednesday, March 27, 2013 12:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 12:17 PM
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 approach
Post #1436085
Posted Wednesday, March 27, 2013 2:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:24 AM
Points: 5,258, Visits: 12,189
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 approach


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.

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 #1436151
Posted Wednesday, March 27, 2013 2:16 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
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.
Post #1436154
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse