question

john-6888 avatar image
0 Votes"
john-6888 asked nasreen-akter commented

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

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

azure-data-factory
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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?)

0 Votes 0 ·

1 Answer

PRADEEPCHEEKATLA-MSFT avatar image
1 Vote"
PRADEEPCHEEKATLA-MSFT answered nasreen-akter commented

Hello @john-6888,

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.

98497-image.png

95169-image.png

98435-adf-specialchar.gif

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.


image.png (109.7 KiB)
adf-specialchar.gif (950.2 KiB)
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @john-6888,

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.

0 Votes 0 ·

Hello @john-6888,

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.

0 Votes 0 ·

Hi @PRADEEPCHEEKATLA-MSFT

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

0 Votes 0 ·

Hi @MohamedMohamed-9702,

You can try regexReplace() e.g.,

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

Thanks!


0 Votes 0 ·

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

157384-capture.png


0 Votes 0 ·
capture.png (49.2 KiB)
Show more comments