Remove special characters from csv file which is presented in azure blob storage

Question

Remove special characters from csv file which is presented in azure blob storage

john 21

i want to remove special character @ symbol present in csv file im trying with escape charater but it is not working

Gajanan Hegde, Nandan 6 Reputation points

2021-05-21T05:03:29.1+00:00

Hey,
Can you please mention the steps which you have tried with your source and sink details and what error you are getting ( if any?)

1 answer

Your answer

Gajanan Hegde, Nandan 6 Reputation points

2021-05-21T05:03:29.1+00:00

Hey,
Can you please mention the steps which you have tried with your source and sink details and what error you are getting ( if any?)

Answer 1

PRADEEPCHEEKATLA 90,661 Moderator

Hello @john ,

Thanks for the question and using Microsoft Q&A platform.

Use the derived column transformation to generate new columns in your data flow or to modify existing fields.

In ADF, you can use the replace expression language to replace a substring with the specified string and return the result string.

For more details, refer Derived column transformation in mapping data flow.

Hope this helps. Do let us know if you any further queries.

------------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.

PRADEEPCHEEKATLA 90,661 Reputation points Moderator

2021-05-24T10:33:19.787+00:00

Hello @john ,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.
PRADEEPCHEEKATLA 90,661 Reputation points Moderator

2021-05-25T11:13:10.117+00:00

Hello @john ,

Following up to see if the below suggestion was helpful. And, if you have any further query do let us know.
Take care & stay safe!

---------------------------------------------------------------------------

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.
Mohamed Mohamed 66 Reputation points

2021-12-12T16:28:05.183+00:00

Hi @PRADEEPCHEEKATLA

Is this only work to remove the character @ ? what if there is other characters I don't know about?

In another way, Im looking to clean phone numbers (3 columns) - there might be special characters, letters and whitespaces, how can i remove all that? Thanks
Nasreen Akter 10,811 Reputation points Volunteer Moderator

2021-12-12T16:52:05.373+00:00
Hi @Mohamed Mohamed ,

You can try regexReplace() e.g.,

regexReplace('1234akdjhf567@dkjgkgj!SDFRC890', '[@!\\sa-zA-Z]', '')

Thanks!
Mohamed Mohamed 66 Reputation points

2021-12-14T08:44:56.017+00:00

Thanks for the above @Nasreen Akter

I think regexReplace('1234akdjhf567@dkjgkgj!SDFRC890', '[@!\sa-zA-Z]', '') created a new column, the out but is '1234567890' for all the rows.

My case, I do have a MobilePhone column, and i want to make sure that there's no whitespaces, letters or special characters in it. Thanks
Nasreen Akter 10,811 Reputation points Volunteer Moderator

2021-12-15T00:19:56.38+00:00

Hi @Mohamed Mohamed ,

In this case, you can just put your columnName instead of the hardcode String e.g.,

regexReplace(MobilePhCol, '[@!\sa-zA-Z]', '')

Thanks!

Share via

Remove special characters from csv file which is presented in azure blob storage

1 answer

Your answer