Adding Index Column in DataTable using LINQ

Help Studio
, , ,
1

Hello Evenryone,

Is there any way to add index column in the datatable and then incrementing row values in added index column by 1,

Input DataTable:

Order Number Name City ID Order Amount
894626 John Singapore STDX-11 ₹ 23,121
893610 Kenrick London STDX-12 ₹ 33,908
893077 Tim New York STDX-13 ₹ 41,359
892141 Jorge Amsterdam STDX-14 ₹ 17,352
893143 David Canada STDX-15 ₹ 39,135
892445 Meena Thailand STDX-16 ₹ 16,886
891725 Arun Ireland STDX-17 ₹ 29,960
892142 Alok Dubai STDX-18 ₹ 42,332
893887 Keshav San Fransico STDX-19 ₹ 25,061
894805 Amit California STDX-20 ₹ 29,208

Output required Datatable,

IndexValue Order Number Name City ID Order Amount
1 894626 John Singapore STDX-11 23121
2 893610 Kenrick London STDX-12 33908
3 893077 Tim New York STDX-13 41359
4 892141 Jorge Amsterdam STDX-14 17352
5 893143 David Canada STDX-15 39135
6 892445 Meena Thailand STDX-16 16886
7 891725 Arun Ireland STDX-17 29960
8 892142 Alok Dubai STDX-18 42332
9 893887 Keshav San Fransico STDX-19 25061
10 894805 Amit California STDX-20 29208
2

@NILESH.BOT369

YourDataTable = YourDataTable.AsEnumerable().Select(Function(row, index) row.ItemArray.Concat({index + 1}).ToArray()).CopyToDataTable()
3

HI @NILESH.BOT369

Assign activity:
outputDataTable = (From row In inputDataTable.AsEnumerable()
                   Let indexValue = inputDataTable.Rows.IndexOf(row) + 1
                   Let orderAmount = Convert.ToInt32(row("Order Amount").ToString().Replace("₹", "").Trim())
                   Select outputDataTable.Rows.Add(indexValue, row("Order Number").ToString(), row("Name").ToString(), row("City").ToString(), row("ID").ToString(), orderAmount)).CopyToDataTable()

Regards

4

@NILESH.BOT369

if you have a build datatable activity having all the columns names

try this once

dt.AsEnumerable().Select(Function(a,i) dt.Clone().LoadDataRow({(i+1).ToString().Concat(a.ItemArray).ToArray()}, False)).CopyToDataTable()
5

we can avoid the LINQ and achieve it by:

  • Build DataTable - Configure an empty DataTable with 1 Col - RowNum, Configure AutoIncrement
    grafik
    dtMarked

Then use a Merge DataTable Activity and merge your DataTable into dtMarked

2 Likes
6

thank you, But what if i dont know how many rows are there in the “dtMarked”?
Because we have to add those many rows in Build Data Table manually?

7

Hi @NILESH.BOT369

→ Build Data Table

MicrosoftTeams-image (1)
MicrosoftTeams-image (1)877×477 11.3 KB

Output-> outputDataTable

→ Read Range Workbook
MicrosoftTeams-image (2)
Output-> dt

→ Use Below syntax in Assign:

outputDataTable = (From row In dt.AsEnumerable()
                   Let indexValue = dt.Rows.IndexOf(row) + 1
                   Let orderAmountString = row("Order Amount").ToString().Replace("₹", "").Trim().Replace(",","").Trim()
                   Let orderAmount As Integer = If(Integer.TryParse(orderAmountString, Nothing), Convert.ToInt32(orderAmountString), 0)
                   Select outputDataTable.Rows.Add(indexValue, row("Order Number").ToString(), row("Name").ToString(), row("City").ToString(), row("ID").ToString(), orderAmount)).CopyToDataTable()

→ Write Range Workbook
MicrosoftTeams-image (3)

Regards

1 Like
8

no, you dont have to place any row in dtMarked:

was also mentioned:

when merging dtData into dtMarked the autoincrement will be fired and the rownum is automatically set/ increased

9

Hello,

if datas are not already in dataTable, I would go for an auto-increment new column.
If datas are already there, i would stay simple with standard activities, not with linq for this purpose.
Add Data Column for IndexValue
For each row in datatable with currentIndex as output variable.
And Updating currentRow column to CurrentRow(“IndexValue”) with currentIndex

Hope this helps

1 Like
10

Got it, thank you,
But, in this case “RowNumber” values will start from 0 when autoincrement will be fired.

11

we can set the start value by:
Assign Activity:
dtMarked.Columns("RowNum").AutoIncrementSeed = 1

do it before the merge datatable

1 Like
12

Works Perfect…!!! :+1: :100:

13

@NILESH.BOT369
Perfect, so the topic can be closed
Forum FAQ - How to mark a post as a solution - News / Tutorials - UiPath Community Forum

14

Thank you for your help, appreciating your help,
But i am marking @vrdabberu 's reply as answer because question was about LINQ and query shared by him works perfectly, Thank you.

15

Thank you,
Happy Automation!

16

Hi @NILESH.BOT369

You’re Welcome.

Happy Automation!!

17

Ok Perfect

Keep in mind that we do have also the below described discrete approach

Configure the Result Datatable with a Build Datatable (manually or along with the help of dtData.Clone) - dtMarked (NumIndex col at the first position)

Assign Activity:

(From i in Enumerable.Range(0, dtData.Rows.Count)
Let ra1 = dtData.Rows(i).ItemArray
Let ra = ra1.Prepend(i+1).toArray
Select r = dtMarked.Rows.Add(ra)).CopyToDataTable

we avoid the IndexOf and grab by index with this approach

For LINQ learning have a look here

For Column Update Options have a look here:

1 Like
18

Hello, thank you so much,
Will try this approach :slight_smile:

19

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.