Count character

Help Activities
, ,
1

I want to count character in column Create ID.

If count character= 7 insert 0 in front of old text.

Please guide me for solve it.
data.xlsx (10.2 KB)

2

You can use For Each Row activity, inside that you can access the Create ID column value as CurrentRow(“Create ID”).ToString, and get the character count inside that string using Length method

CurrentRow(“Create ID”).ToString.Length

1 Like
3

image
This is input file
and Below is the output
image
Count_Char.zip (41.7 KB)

1 Like
4

Hi,

Try below linq

Assign activity:
ResultDataTable = (From row In YourDataTableVar.AsEnumerable()
Let value = row.Field(Of String)(“ColumnA”)
Let newValue = If(value.Length = 7, “0” + value, value)
Select YourDataTableVar.Clone().Rows.Add({newValue}).CopyToDataTable()).CopyToDataTable()

5

@Jayesh_678

I have a question.

If I want to sort column Planned Start (ascending)
How to edit your code?

Thank you.

6

Hi @fairymemay

Try this:

ResultDataTable = (From row In YourDataTableVar.AsEnumerable()
                   Let value = row.Field(Of String)("ColumnA")
                   Let newValue = If(value.Length = 7, "0" + value, value)
                   Order By row.Field(Of DateTime)("Planned Start") Ascending
                   Select YourDataTableVar.Clone().Rows.Add({newValue}).CopyToDataTable()).CopyToDataTable()

Hope it helps!!

7

@parvathy Now I have error as below.

How to solve it?

image
image939×282 26.3 KB

8

Hi,

Here is the modified linq query:-

ResultDataTable = (From row In YourDataTableVar.AsEnumerable()
Let value = row.Field(Of String)(“ColumnA”)
Let newValue = If(value.Length = 7, “0” + value, value)
Select YourDataTableVar.Clone().Rows.Add({newValue})
Order By DateTime.ParseExact(row.Field(Of String)(“Planned start date”), “dd/MM/yyyy”, CultureInfo.InvariantCulture)
).CopyToDataTable()

9

Hi @fairymemay

Pass your datatable variable in place of YourDataTableVar.
Regards

10

@Jayesh_678 @Parvathy error as below.

image
image931×320 29.7 KB

11

Hi,

Import below dependencies from import tab

Imports System.Data
Imports System.Globalization

Thanks

12

Replace Column A with your Id column name , replace planned start date with your planned start date column. Column names are case sensitive.

Thanks

1 Like
13

Hi @fairymemay

What is your output variable DataType?

ResultDataTable = (From row In YourDataTableVar.AsEnumerable()
                   Let value = row.Field(Of String)("ColumnA")
                   Let newValue = If(value.Length = 7, "0" & value, value)
                   Order By DateTime.ParseExact(row.Field(Of String)("Planned Start"), "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture) Ascending
                   Select YourDataTableVar.Clone().Rows.Add({newValue}).CopyToDataTable()).CopyToDataTable()

Hope it helps!!

14

@parvarthy @Jayesh_678 error as below.

image

image
image931×320 29.7 KB

1 Like
15

Hi,

Give me some time. I will provide you a xaml file.

Thanks

1 Like
16

@Jayesh_678 if I want to more filter column Owner = IT or Eng.

How to edit code?

18

@Jayesh_678 @parvarthy Now error as below.

image
image930×315 26.1 KB

How to solve it?