How to replace double quotes in derived column transformation?

Question

I have flat file. I am loading data from flat file to source table using ssis.And one of the column has following values:

<Somecol1 =""1"" col1values= ""223,567,890,653"">

I want following column output:

<Somecol1 ="1" col1values= "223,567,890,653">

I have tried to replace in derived column.

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

but this doesnt work.

Airdrome

Answer 1

I think you have almost got the expression correct except for the additional slash in the replacement string. Here are the expressions that might work for you.

Expression #1: Removes all double quotes within a given string.

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

Expression #2: Replaces all double occurrences of double quotes with single occurrence of double quotes.

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

Here is an example that demonstrates expression #1:

1. Screenshot #1 shows the CSV file that will be read by a package.
2. Screenshot #2 shows the Derived Column transformation inside the Data Flow task that will replace all double quotes within the first column named as Header.
3. Screenshot #3 shows data in the table after the package execution. Notice that the double quotes in second column are left as it is because there is no expression to replace them.

Here is an example that demonstrates expression #2:

1. This example will use the same file as in example 1. Refer screenshot #1.
2. Screenshot #4 shows the Derived Column transformation inside the Data Flow task that will replace all double occurrences of double quotes with single occurrence of double quotes within the first column named as Header.
3. Screenshot #5 shows data in the table after the package execution. Notice that the double quotes in second column are left as it is because there is no expression to replace them.

Hope that helps.

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Lonesome

What if the flat file source connection fails to process the file? you don't get passed the first step and therefore can't make it to the Derived Column transformation. – Footfall

If you want to replace double quotes with empty string in the SSRS RDL report, try this, it will work! Replace(NAME,chr(34),"") – Carleycarli

Answer 2

This may not work if your other columns don't have this same issue, but if this is the only text column you are importing or they are all like this, you could change your text identifier to be two double quotes instead of one. Then SSIS would bring it out of the flat file correctly and you wouldn't have to deal with trying to clean it up later.

Hairline

I have already added the text identifier as double quotes. After the text qualifier the column looks like <Somecol1 =""1"" col1values= ""223,567,890,653""> please consider this as a single column – Airdrome

@Airdrome - Ahhh, ok I understand now. I was seeing the column data as two columns of data. Thanks for the clarification. – Hairline

Answer 3

Please find below samples

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Client_1,"\"",""),"*",""),",",""),"[",""),"]",""),"'",""),"!",""),"/",""),"<>","")

Nicobarese

You've got to be kidding me! – Gentry