Remove double quotes from header

Question

I have a csv file coming with column names and column values are in double quotes. The csv file resides inside Azure Blob and for that I am using Azure blob source task (downloaded from azure featurepack). I need to load this file using SSIS to sql and don't want those double quotes from COLUMN NAMES as well as for COLUMN VALUES. Here is how it looks.

Unlike Flat file connection manager, I can not use " as text qualifier so that it will get rid of those double quotes. Because its not there in Azure Blob Source task.

"ID","Name","Phone"
 "1", "sam","11-911-9111"
 "2","ham","01-101-1011"

How do i get rid of it using ssis? Thanks

Any update now? If it helps you, please accept it as answer.(click on the check mark beside the answer to toggle it from greyed out to filled in.) — Joey Cai, Oct 25 '19 at 02:27

score 0 · Answer 1 · answered Oct 23 '19 at 07:53

Use Replace function to get the expression correct except for the additional slash in the replacement string. Refer to this SO thread.

1.Removes all double quotes within a given string.

REPLACE(COLA, "\"", "")

2.Replaces all double occurrences of double quotes with single occurrence of double quotes.

REPLACE(COLA, "\"\"", "\"")

Or you can use Text Qualifier in SSIS.

Remove double quotes from header

1 Answers1