Informatica: dealing with double quotes using expression tranformation

Question

I am developing a mapping in IICS CDI which takes API response as source and save it to a comma delimited .txt file (flat file target).

In the response from the API, since some of the fields contains comma sign "," that field will be surrounded with double quote in order to keep the csv format. for example, below is a sample row from the original API response:

Rueben Enns,999,B,2023-05-25 15:12:52, "I-294 / I-80 N, EAST HAZEL CREST, IL"

However, I don't know why, when I check on the output file from the mapping, this kind of fields are added with a repeated double quote, below is a sample row from the mapping target:

Rueben Enns,999,B,2023-05-25 15:12:52, ""I-294 / I-80 N, EAST HAZEL CREST, IL""

Then I tried different ways to replace the two double quotes with one double quote, below are the methods I tried:

1. REPLACECHR(output_string, '""', '"')
2. REG_REPLACE(output_string, '"+"', '"')

But they all don't work, the two double quotes are still two double quotes.

Besides this, another thing to note is that in the flat file output of the mapping, the whole string is also surrounded with double quote, please refer to this post (https://network.informatica.com/s/question/0D56S0000EGXHe9SQH/how-to-remove-the-double-quote-sign-from-the-string-output-result) for more details.

Hence I need to remove single double quote while replacing multiple double quote to single double quote. i.e. remove the double quote if its occurrence is once, replace the double quotes with single double quote if its occurrence is twice

Now the case is more complicated, because I need to make sure that when I remove the single double quote, I don't also remove multiple double quote. Instead, I need to convert multiple double quote to single double quote.

Could anyone give me some advice as how to deal with these double quotes? Thank you so much for your help!

Answer 1

can you pls try this -

1. go to target properties in session.
2. click open set file properties
3. follow below screenshot to set optional quotes to None.
4. click ok, save the session and run the workflow.