2

I have a csv which has a column called "Value" and in it there are a combination of strings & special characters.

Eg:

Value
abc
xyz
"
pqr
'

I want to replace the " and ' special characters with empty string "" in the output file.I used the derived column "Replace" function as 

Replace(Replace(Value,"'",""),"\"","")

and it doesn't seem to work

tried this: in the derived column 

Replace(Replace(Value,"'",""),"\"","")

I expect the output to be in a flat file with value column as

Value
"abc"
"xyz"
""
"pqr"
""
Hadi
  • 36,233
  • 13
  • 65
  • 124
AtuD
  • 83
  • 2
  • 11
  • The derived column expression seems to be correct. What was the output? What do you mean by `and it doesn't seem to work`? – Hadi Apr 05 '19 at 14:48
  • Did u changed the destination mapping to point to the derived column ? – Srikar mogaliraju Apr 05 '19 at 15:17
  • Yes, I mapped the new column to the output column. – AtuD Apr 05 '19 at 15:47
  • in the output file,no change in ' to " " – AtuD Apr 05 '19 at 15:47
  • 1
    can you add a data viewer before the derived column and after the derived column and crosscheck the output from both the data viewers? – Srikar mogaliraju Apr 05 '19 at 15:54
  • https://stackoverflow.com/questions/55268872/ssis-removing-single-quote-within-double-quotes/55269082#55269082 – Hadi Apr 05 '19 at 16:19
  • Thanks Hadi for the link. that link works when there is both " and ' in the same field of the column. I'm looking for two different replace functions, where in one field i have " and other field I have '. I need both of them to be replaced by empty string – AtuD Apr 05 '19 at 16:26
  • it seems like length might be an option. LENGTH(value)==1?"":value But i agree with others, your derived column looks correct as well. – KeithL Apr 05 '19 at 16:41
  • @atulyadharmaraj it works in both cases – Hadi Apr 05 '19 at 16:54
  • And you're sure that the ' is the same character in the file and derived column expression? As mentioned your derived column seems fine. This should work the same as the derived column, but just curious, what happens if you trying using a script component instead? I.E. `Row.Value = Row.Value.Replace("\"", "").Replace("'", "");` – userfl89 Apr 05 '19 at 18:07

1 Answers1

1

I want to replace the " and ' special characters with empty string ""

If you are looking to remove all quotations from all values then the derived column expression you are using is fine:

Replace(Replace(Value,"'",""),"\"","")

Since you mentioned that it doesn't seem to work, then you have to check many possible causes:

  1. Check that the quotation is ' not `, try using the following expression

    Replace(Replace(Replace(Value,"'",""),"\"",""),"`","")

  2. If your goal is that all values in the destination flat file are enclosed within two double quotes: "abc", "" then you should edit the Flat File connection manager of the destination and set " as text qualifier and make sure that columns qualified property is set to true.

  3. As @Srikarmogaliraju mentioned, make sure that the derived column output is mapped in the destination
Hadi
  • 36,233
  • 13
  • 65
  • 124