April 22, 2009 at 10:46 am
I have 30 Excel files with millions of rows in each that contain double quotes " around each data field. I plan to use SSIS to load the data into a table. I'd like to strip the double quotes out before loading into my destination table.
I've been trying to use the REPLACE function in the DERIVED TRANSFORMATION but can't get the quotes removed. Any suggestions would be appreciated.
Here's what I've tried so far that hasn't worked:
REPLACE(COLUMN_1, ""","") double quote surrounded by double quotes
REPLACE(COLUMN_1, '"',"") single quotes surrounding the double quote
REPLACE(COLUMN_1, "\"","") backslash to mask the quote
REPLACE(COLUMN_1, "/"","") forward slash to mask the quote
Any suggestions would be appreciated.
April 22, 2009 at 11:06 am
There's an option in SSIS in the import control that allows you to tell it what character is used to begin and end fields. That's in addition to a delimiter. You might be able to use that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 22, 2009 at 12:43 pm
Text Qualifiers are only availble for flat files. Try tying the following syntax for replacing
REPLACE([Column 1],"\"","")
you just need to escape the double quote with a backslash
HTH
Mukti
April 22, 2009 at 12:55 pm
G - thanks! In the flat file setup I entered " in the Text Qualifier box. This fixed my issue. After executing an SSIS package, the data loaded does not contain quotes!
April 22, 2009 at 12:56 pm
SSC - thanks, but that didn't work. See my previous reply on what did work. Thanks for attempting to help.
April 22, 2009 at 2:30 pm
You're welcome. Glad we could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply