Replace value - with 0 in datatable

I have one datatable in one column having blank value or - , I want to replace blank and “-” with “0” how can do this using LINQ query.

Hi @Anil_Potekar

Try this:

dtCorrected = dtData.Clone()

dtCorrected= (From r In dtData.AsEnumerable
 Let ra = r.ItemArray.Select(Function(x) If(String.IsNullOrWhiteSpace(x.ToString) OrElse x.ToString.Trim = "-", "0", x.ToString.Trim)).ToArray()
 Select dtCorrected.Rows.Add(ra)).CopyToDataTable()

Hope it helps!!

Hi @Anil_Potekar

I have used Invoke Code activity and tried to replace - or empty in datatable with 0. Please have a look at the attachment .Hope it helps .


Another way

Use invoke code and send dt as in/out argument and use below

Dt.AsEnumerable.Where(function(x) IsNothing(x("ColumnName")) OrElse String.IsNullOrEmpty(x("ColumnName").ToString.Trim) OrElse x("ColumnName").ToString.Trim.Equals("-")).ToList.ForEach(Sub(r) r("ColumnName") = "0")

1 Like

Need to store in double


try this linq

Assign activity:

dtYour_DataTable = dtYourDataTable.AsEnumerable().Select(Function(row) 
{row(0).ToString().Trim() = "" OrElse row(0).ToString().Trim() = "-" ? "0" : row(0).ToString().Trim()}).CopyToDataTable()


query = From row As DataRow In dataTable.AsEnumerable()
Let value = row.Field(Of String)(columnName)
Select New With
.Row = row,
.Value = If(String.IsNullOrWhiteSpace(value) OrElse value = “-”, “0”, value)

Try This:

Hi @Anil_Potekar ,

Could you share a dummy datatable with sample data in it and your expected result.
Share the table column datatype if you have any specific.
Do you want to store 0 or “0” in that column?

Geetishree Rao

Hi @Anil_Potekar

If String.IsNullOrWhiteSpace(row(“YourColumnName”).ToString()) OrElse row(“YourColumnName”).ToString() = “-” Then
row.SetField(“YourColumnName”, “0”))