Replace value - with 0 in datatable

Help Studio
, , , , ,
1

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.

2

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!!

3

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 .

ReplacingEmptyWith0 in datatable
ReplacingEmptyWith0 in datatable1468×300 10.7 KB

4

@Anil_Potekar

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
5

Need to store in double

6

@Anil_Potekar

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()

cheers…!

7

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:

8

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?

Regards,
Geetishree Rao

9

Hi @Anil_Potekar

YourDataTable.AsEnumerable().ToList().ForEach(Sub(row)
If String.IsNullOrWhiteSpace(row(“YourColumnName”).ToString()) OrElse row(“YourColumnName”).ToString() = “-” Then
row.SetField(“YourColumnName”, “0”))