Checking special characters in column values in data table

Help
2

Hi,

As far as I know, you can’t manipulate dataTables with LINQ or other efficient methods, so ForEach is only way (if you use it as a Data Table). However, I know you can do this if you convert the table to a string because then you can use the Select function to piece the string together per row. It’s probably not an ideal solution, but does work.

Here is an example of what I mean…

1. Output Data Table to string (let’s call “strTable”)

2. Use vb.net expression to query array.
strTable = String.Join(System.Environment.Newline,strTable.Split(System.Environment.Newline(0)).Select(Function(row) If(row.ToString.Split(“,“c)(0).Contains(”*”) or row.ToString.Split(“,“c)(0).Contains(”'”),row.ToString.Split({row.ToString.Split(",“c)(0)},System.StringSplitOptions.None)(0)+newname+row.ToString.Split({row.ToString.Split(”,"c)(0)},System.StringSplitOptions.None)(1),row.ToString)).ToArray)

3. Either output string to Text File with .CSV extension and Read Range back to DataTable, or use Generate Data Table activity.

Notes: I used row.ToString.Split(",“c)(0) to signify that the first column is used with (0); if you would like the semployerName column then either hardcode the column Index or use I think
dtvariable.Columns.IndexOf(“semployerName”)

.Select function I used says “go through each line in the strTable, and if value contains “*” or “'” then split string up, replacing value with your newname, and concatenating it back together… if it doesn’t contain those values then it just keeps the line as is.”

Sorry for any syntax or typo errors.

Regards!

1 Like