Adding a 0 to the front to values in the column of datatable

Learning Hub Academy Feedback
, ,
1

is there a way without using forloop, to add 0 to values of a datacolumn “A”, if the values has count less than 3?

e.g.

A
22
33
123
23

to

A
022
033
123
023

2

@TyraS

(From row In dt.AsEnumerable()
Let val = row(“A”).ToString()
Let newVal = If(val.Length < 3, val.PadLeft(3, "0"c), val)
Select row.SetField(“A”, newVal)).CopyToDataTable()

3

if count < 3

for each row in datatable

assign “0” + row

Regards

4

Hello @TyraS , try this:

dt = dt.AsEnumerable().Select(Function(row) row(“A”).ToString().PadLeft(3, "0"c)).CopyToDataTable()

5

Hi @TyraS

How about this expression?

dt = (From row In dt.AsEnumerable()
      Let value = row("A").ToString()
      Let newValue = If(value.Length < 3, value.PadLeft(3, "0"c), value)
      Select row.Field(Of Object)("A") = newValue).CopyToDataTable()

Regards
Gokul

6

Hi @TyraS ,

We could also try with DataColumn Expression since the conditions involved are Simpler :

  1. Add a new Data Column using Add Data Column activity, lets say Modified Column.

    image
    image743×420 21.8 KB

  2. Next, we can use the Data Column Expression to perform the updation for this new column based on the values of the First Column like below :

DT.Columns("Modified Column").Expression = "IIF(LEN(CONVERT([Column1],'System.String'))<3,'0'+CONVERT([Column1],'System.String'),[Column1])"
  1. Since it is an update on Existing Column, we may have to use DefaultView.ToTable() to keep only the required Columns and it can be done like below :
DT = DT.DefaultView.ToTable("Modified Column")

Then Change the column name to the previous :

DT.Columns("Modified Column").ColumnName = "Column1"

Debug Visuals :
image