Linq Query to Remove all Spaces from specific column in Data Table

Help Studio
, ,
1

Hi All,

I want to remove all spaces from a specfic column in data table using linq query.
I want operation to be done in same data table variable instead of new variable.

Col1 Col2 Col3
as df gh gt ab cd as df

Output SHould be

Col1 Col2 Col3
asdfghgt ab cd as df

Can someone please help

2

@JITU99

  1. Use the “Assign” activity to assign the column name to a variable. Let’s say the variable is named columnName.
  2. Use the “For Each Row” activity to iterate through each row in the DataTable.
  3. Inside the loop, use the LINQ query to remove spaces from the specific column. You can use the Replace method of the string to replace spaces with an empty string. The LINQ query will update the value in the same DataTable.Here’s an example of the LINQ query:

vbCopy code

dt.AsEnumerable().ToList().ForEach(Sub(row) row(columnName) = row(columnName).ToString().Replace(" ", ""))

Replace dt with the name of your DataTable variable.
4. After the loop is completed, the DataTable will have the specified column with spaces removed.

4

I am getting this error while putting the code inside for eac row.

image
image869Ă—291 12.3 KB

5

the prososed sub method

is a subrroutine and is not returning a value (void method). This is expressed by the compile message and the code statement cannot be used within an assign activity.

Also have a look here:

6

You can simplify your expression like this:

image

Where:
1 = CurrentRow(columnName)
2 = CurrentRow(columnName).ToString.Replace(" ","")
And columnName is pre-assigned to the name of the column where you want to remove the spaces

7

Hi,

Can you try the following sample?

image
image715Ă—434 17.9 KB

dt = dt.AsEnumerable.Select(Function(r) dt.Clone.LoadDataRow({r(0).ToString.Replace(" ",""),r(1),r(2)},False)).CopyToDataTable()

Sample20230626-7L (2).zip (8.1 KB)

Regards

8

Hi @Yoichi what if i dont know no. of rows.How should i modify the query to?

9

In this case, InvokeCode might be better as the following.

image

dt.AsEnumerable.ToList().ForEach(Sub(r)
    r("col1")=r("col1").ToString.Replace(" ","")
End Sub
)

Sample20230626-7L (3).zip (6.8 KB)

Regards,

10

Thanks its working.

1 Like
11

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