Excel column filled with text replaced by empty spaces using linq

Hi all,

If excel column has some text data that needs to replaced with blank using linq query. Please check the below attached screenshot.
image
In the above screenshot the highlighted part has to be replaced with empty spaces.
image
How to do this using linq.
Please help me on this.

Hi,
Why do you specifically need LINQ?
Also, what is the criteria for replacing those cells?
IF linq is not required you can simply use for each row in datatable and then ,if your cell contains"Complete" or “parts sales”. then assign it blank.

Try this
put the string in Varstring then
For Each row As Datarow In dt.AsEnumerable().Where(Function (r) (r(“Column1”).tostring.equals(“varstring”)).replace(“varstring”,“”)).copytodatatable

if you face any issue let me know
happy automation.

dt.asenumerable.tolist.foreach(sub(row) row(“Column1”)= row(“Column1”).tostring.replace(“,”,“.”))

1 Like

Hi @Usha_Jyothi ,

Should I want to store this

For Each row As Datarow In dt.AsEnumerable().Where(Function (r) (r(“Column1”).tostring.equals(“varstring”)).replace(“varstring”,“”)).copytodatatable

in assign activity?

yes you can use in assign activity
variable data type as datatable

Hey @lakshmi.mp ,

Use the following linq expresion:

Assign dt_Out = (From row In dt_Out
                 Let a = If((row("Column1").ToString.Contains("Complete") Or (row("Column1").ToString.Contains("Part")),"",row("Column1"))
                 Select dt_Out.Clone.Rows.Add({row("Column0"),a})).CopyToDatatable

dt_Out is the datatable you use to read the excel. Let me know if it works

Are you sure you want to replace the value as Blank or maybe you want to filter out those rows with “Complete” or “Parts Sales” values? If that’s the case, you can use Filter Data Table activity.

But I have a solution on your original problem. Check this process workflow:
Replace Blank Value.zip (2.0 KB)

Assign dtOutput = 
(
From sales In dtSales
	Let ColumnA = sales("A").ToString
	Let ColumnB = If(sales("B").ToString.ToUpper.Contains("COMPLETE") Or sales("B").ToString.ToUpper.Contains("PARTS SALES"), String.Empty, sales("B").ToString)
Select dtOutput.Rows.Add(ColumnA, ColumnB)	
).CopyToDataTable

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