How to String Replace Data in Datatable, for selected Columns only

Help Robot
1

I’m trying to string replace dashes in datatable with a space.
For reference here’s the code that using

(From r In in_dt_InputFileAsEnumerable
Let ra = r.ItemArray.Select(Function (x) x.ToString.Trim.ToUpper.Replace("-","").toArray()
Select out_dt_StringReplaced.Rows.Add(ra)).CopyToDataTable()

Now, I want to do the same to another datatable but I need to exclude 2 columns from string replacing. I can do it column by column but I have around 30 columns in the table. Can I still use the same code but with column exclusion?

2

Hi @Archie ,

As you would want to exclude the required column values from getting String Replaced. We would need to Collect the name of those columns separately in an Array or a List.
You could try the below Steps :

  1. Initialise an Array to Store the Columns to Exclude :
columnsToExclude = {"Column1","Column2"}

here columnsToExclude is a variable of Type Array of String.

  1. Next, Collect All Column Names present in the Datatable and store it another array of String :
allColumnNames = DT.Columns.Cast(Of DataColumn).Select(Function(c_name)c_name.ToString).ToArray

here allColumnNames is a variable of Type Array of String.

  1. Next, we modify your Linq Query above to get Replace only the Column Values required like below :
(From r In in_dt_InputFile.AsEnumerable
Let ra = allColumnNames.Select(Function(c_name)If(columnsToExclude.Contains(c_name),r(c_name).ToString.Trim,r(c_name).ToString.Trim.ToUpper.Replace("-",""))).toArray()
Select out_dt_StringReplaced.Rows.Add(ra)).CopyToDataTable()

Let us know if this doesn’t work.

Also Note, there are some mistakes in the Linq Query you have provided above (Brackets and . are not added)

1 Like
3

I got Object reference not set for the LINQ.

4

@Archie ,

Apologies,

Find the Updated Query Below :

allColumnNames = in_dt_InputFile.Columns.Cast(Of DataColumn).Select(Function(c_name)c_name.ColumnName.ToString).ToArray
5

Found it, its my mistake sorry. The output datatable from LINQ was not initialized, had to clone it first from the main datatable.

1 Like
6

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