CODEWITHSUNDEEP
sql-server-2008ssis
DavidStein
DavidStein

Reputation: 3179

How to remove double quotes surrounding the text while importing a CSV file?

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?

Upvotes: 10

Views: 33862

Answers (4)

user3050134
user3050134

Reputation: 1

I would rather use the following statement....

REPLACE(REPLACE(REPLACE(ColumnName, '""', '[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-

Upvotes: -1

Suresh Shiva
Suresh Shiva

Reputation: 1

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

Upvotes: 0

Sonali Jain
Sonali Jain

Reputation: 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

Upvotes: 0

DavidStein
DavidStein

Reputation: 3179

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.

Flat file in Notepad++

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

General tab of the flat file connection manager

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

Columns tab of the flat file connection manager

Upvotes: 23

Related Questions