How to remove double quotes surrounding the text w

Question

I have data which resembles the following:

"D.STEIN","DS","01","ALTRES","TTTTTTFFTT" 
"D.STEIN","DS","01","APCASH","TTTTTTFFTT" 
"D.STEIN","DS","01","APINH","TTTTTTFFTT" 
"D.STEIN","DS","01","APINV","TTTTTTFFTT" 
"D.STEIN","DS","01","APMISC","TTTTTTFFTT" 
"D.STEIN","DS","01","APPCHK","TTTTTTFFTT" 
"D.STEIN","DS","01","APWLNK","TTTTTTFFTT" 
"D.STEIN","DS","01","ARCOM","TTTTTTFFTT" 
"D.STEIN","DS","01","ARINV","TTTTTTFFTT" 

I've used a Flat File Source Editor to load the data. What is the easiest way to remove all of the double quotes?

Answer 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

Answer 2

substring([column 5], 2,(len([column 5])-2) )

Answer 3

I would rather use the following statement....

REPLACE(REPLACE(REPLACE(CoulumnName,'""','[YourOwnuniqueString]'),'"',''),'[YourOwnuniqueString]','"')

Note: Please make sure your YourOwnuniqueString should be unique and not used any where in the columns as data. E.x: SQL@RT2#myCode -It is case sensitive-

Answer 4

Further searching revealed that I should use the Text Qualifier on the General Tab of the Flat File Source.

Flat file content when viewed in a Notepad++. CRLF denotes that the lines end with Carriage Return and Line Feed.

On the flat file connection manager, enter the double quotes in the Text qualifier text box.

Once the text qualifier is set, the data would be parsed correctly and displayed as shown below: