How to remove special characters in excel data table like for one sheet

  1. ,

  2. $

  3. ;

  4. /

image

Hi @sathish_Kumar6

Perform a for each row in excel and then use

currentRow("Columnname") = System.Text.RegularExpressions.Regex.Replace(currentRow("Columnname").ToString,"[^a-zA-Z0-9\s]","")

This will replace all values except the specified which are alphabets numbers and spaces

Cheers

1 Like

Hi anil i knew little bit regex but how [^a-zA-Z0-9\s] these type patterns remove special characters? and also i have upto fL column so is there any shortcut to apply for entire column range like upto FL?

Hi @sathish_Kumar6

  1. So in the regex cap is for ignore the next characters and what characters i gave are alphabets(a-z and A-Z) , numbers (0-9) , any space characters(\s) .so ignoring these characters all other characters will be replaced

  1. If you wnat to do for all columns then i would suggest read the data into a datatable and then use for loop on row and another loop on column inside the row loop and replace it

Then write back the data

Other way is to do with linq queries but again you will end up using loop inside the linq…

If you need a linq then use this in assign and make sure you clone before dtout = dt.Clone()

(From r In dt.AsEnumerable
Select ra = r.ItemArray.Select(Function (x) System.Text.RegularExpressions.Regex.Replace(x,"[^a-zA-Z0-9\s]","")).toArray
Select dtout.Rows.Add(ra)).CopyToDataTable()

Let me know if you need any other way or help …

Cheers

1 Like

thanks for regex explanation need to learn more about regex , i used loop concept to my code and it worked but idk how to clone and use linq

@sathish_Kumar6

Try regex here

And for linq…give both the command that i gave in 2 assigns …

The first one will clone in the sense it will give the dt structure or column details to dtout

And the next steps will loop through every cell and update the cells and eventually creates a new datatable (as enumerable converts datatable to a datarow collection and itemarray converts row to an array of items)

But yes double loop is better to read…linq is used only for big or very high data

1 Like

wow really impressed with that linq explanation… for two days my code was working but suddenly looping is working but output is not coming may i know y?

@sathish_Kumar6

Not sure why it is…we have to look it to it…run the bot in debug and check if something is didfferent or if data is getting updated in the datatable…use debug and step into

Cheers

1 Like

okay will do debug and step into, thanks for the tip

data is coming from excel but manipulation is not working but it was working before

@sathish_Kumar6

Can you check if something changed in it …whatever manipulation that you fee is not working…copy that and try doing the same in immediate panel and check

Cheers

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.