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
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?
- 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
- 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 …
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
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
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?
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
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
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
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.