How to assign decimal value in particular column value

Help Activities
, ,
1

Hi Team,

I need to assign decimal or number found in column should update in column value ,

I have used one row only for this datatable.

If value come 0.00 also should assign in that column

If not found any decimal value in that column should assign “null” and remove below all rows

Please help me anyone for this.

Input:
image
or
image

Expected output:

image

Regards,
Raja G

2

Hi @Raja.G

=> Read Range Workbook
Output → dt

=> Use the below code kn Invoke code:

dt = dt.AsEnumerable().Take(1).Select(Function(row)
    row.ItemArray = row.ItemArray.Select(Function(cell, index)
        If Decimal.TryParse(cell.ToString(), Nothing) Then
            Return Decimal.Parse(cell.ToString())
        ElseIf dt.AsEnumerable().Skip(1).All(Function(r) Not Decimal.TryParse(r(index).ToString(), Nothing)) Then
            Return DBNull.Value
        Else
            Return cell
        End If
    End Function).ToArray()
    Return row
End Function).CopyToDataTable()

Invoke Code Arguments:
dt: Direction = In/Out, Type = DataTable

=> Write Range Workbook dt back to excel.

Hope it helps!!

1 Like
3

Hi @Parvathy ,

I found error “No Compiled code to run error BC3639: ‘ByRef’ Parameter ‘dt’ cannot be used in a lambda expression At line 5.”

Regards,
Raja G

4

Hi @Raja.G

Try this code in Invoke Code:

updatedTable = dt.Clone()
Dim firstRow As DataRow = dt.AsEnumerable().First()

For Each col As DataColumn In dt.Columns
    Dim foundNumeric As Boolean = False
    Dim numericValue As Decimal

    For Each row As DataRow In dt.Rows
        If Decimal.TryParse(row(col).ToString(), numericValue) Then
            firstRow(col) = numericValue
            foundNumeric = True
            Exit For
        End If
    Next

    If Not foundNumeric Then
        firstRow(col) = DBNull.Value
    End If
Next

updatedTable.Rows.Add(firstRow)
dt = updatedTable

Invoke Code Arguments:
dt : Direction = In, Type = DataTable
updatedTable : Direction = Out, Type = DataTable

Hope it helps!!

1 Like