ADF data flow expression to remove line feeds, carriage return, horizontal tab, vertical tab, new line

N2120 81

Hello, I have a column from a table which has all the special characters . Can you please help me find a data flow expression to take off all the above special characters
I used regexreplace and replace() but output file still had the carriage return and line feeds.
pls advice asap.
thanks

4 answers

Suba Balaji 11,186 Reputation points

2022-12-03T16:35:05.13+00:00

Hi @N2120

Thanks for the ask. Please check this Link in SO, which discusses the similar question.

If you need further help on this please feel free to write back.

Thanks
Please sign in to rate this answer.
N2120 81 Reputation points

2022-12-03T17:31:55.343+00:00

@Suba Balaji Thanks for your response. I am specifically looking for data flow expressions and not activities expressions like shown in the example. I have a data flow and I need an expression in derived column which would help replace special characters listed(line feeds, carriage return, horizontal tab, vertical tab, new line) . replace(column name,'\n','') (or \v,\n\t )is not working in my case
Sign in to comment
MartinJaffer-MSFT 26,011 Reputation points

2022-12-06T03:44:35.953+00:00
Hello @N2120 could you please clarify how it is not working? What is it doing instead?

I tried

replace({_col0_}, '\t', 'TAB')

and saw results.

replace({_col0_}, '\t', '')

became much harder to see. I have a hunch, a change is being made, but it isn't showing up well in the data preview. I could be wrong. That's why I started with 'TAB'.

Another option is to use regexReplace because it can do all those characters at once. Both of the below showed results for me.

regexReplace({_col0_}, `[\t\v\n\r]`, 'X') regexReplace({_col0_}, `[\t\v\n\r]`, '')
Please sign in to rate this answer.
N2120 81 Reputation points

2022-12-06T05:28:54.01+00:00

Thanks for answeriing.
I still see the carriage return and new line feed to the next line even after applying the replace function.

Suba Balaji 11,186 Reputation points

2022-12-06T05:37:53.75+00:00

Did you try replace or regex replace??

Could you please post screenshot and/or expression used in data flow

N2120 81 Reputation points

2022-12-06T05:45:07.833+00:00

@MartinJaffer-MSFT - In the above expression only the \t (tab) worked with the replaced word I put. The rest didn't work.

I tried the below expression and still It didnt replace carriage returns and line feeds
regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(Col1,[\n],''),[\r],''),[\v],''),[\t],''),[\r\n],'')

Tried just replacing the CRLF but didnt work.
replace(Col1,[\n\r],'')

N2120 81 Reputation points

2022-12-08T03:20:12.723+00:00

@Suba Balaji I have tried both replace, regexReplace()
Please see my screenshot

I have carriage return and line feed both \n, \r nothing works, i still see part of the data in the next line.
Sign in to comment
Suba Balaji 11,186 Reputation points

2022-12-06T03:47:29.517+00:00

Hi @N2120 ,

please find below the screenshot where i have tried to replace \t, \r, \n, \v with an empty string. It works fine for me. Please try and revert if you have questions.
Please sign in to rate this answer.
N2120 81 Reputation points

2022-12-06T05:40:21.373+00:00

@Suba Balaji . Thanks for your answer. The issues I am having is special characters in the column from a table. Can you please test if there is a carriage feed going to next line when the expression is working, all data should appear in same line.
It is still not working for me. I do see data going to new line.

Suba Balaji 11,186 Reputation points

2022-12-06T05:58:08.25+00:00

Newline character is replaced but you still see the text broken to multiple lines?

Where is it happening? Is it SQL sink?
Did data preview show it without Line breaks?

Please clarify.

N2120 81 Reputation points

2022-12-08T03:29:38.6+00:00

@Suba Balaji Yes the text is still broken into next line. how do I resolve that?
Data preview also shows with the data in next line.
Derived column expression doesnt seem to work.

please see data in preview

Suba Balaji 11,186 Reputation points

2022-12-08T03:38:45.987+00:00

Hi

What's your source. Is it SQL Table? If so you can fix it using a Script activity (an update statement) and then pass it on to the data flow activity.

N2120 81 Reputation points

2022-12-08T05:07:51.85+00:00

@Suba Balaji No Source system is SAP.
Sign in to comment
N2120 81 Reputation points

2022-12-08T04:35:39.44+00:00

@Suba Balaji @MartinJaffer-MSFT
I am at a loss to find alternate solution,
will anything of these expressions work with replace() to recognize the carriage return '%0A' or '%0D'
I also have a copy activity before this data flow, one of the columns of this output of copy activity is the source of the special characters.
Is there a place in json code where I can do replace in the mapping of copy activity. Variable wont work for me as value will change in that column for each row. (my source system is SAP)
pls advice.
Please sign in to rate this answer.
MartinJaffer-MSFT 26,011 Reputation points

2023-01-26T22:06:18.35+00:00

Would you like to open a support ticket? If you do not have a support plan, I can give you a 1-time free support ticket.
Sign in to comment