Filter Data Table, keeping the original row numbers in Excel

Help
1

I would like to use Filter Data Table activity on DataTable, but keeping the original row numbers before the filtering. For example, say there is an Excel file like following, for which you want to use Read Range in order to get DataTable:

[Excel Row Number …] … ID… Name… Age
[… … … 1 … … … … ]…1213 …John… … 20
[… … … 2 … … … … ]…1235 …Mary… … 28
[… … … 3 … … … … ]…1411 … Tony… … 24
[… … … 4 … … … … ]…1315… Michael … 31

Assume that the retrieved DataTable will not contain the Excel row numbers. Also, there is no column that keeps track of Excel row numbers.
Now you want to filter this DataTable by “Age >= 25”, using Filter Data Table activity. Then the result should be

[Excel Row Number …] … ID… Name… Age
[… … … 2 … … … … ]…1235 …Mary… … 28
[… … … 4 … … … … ]…1315… Michael … 31

I want to keep these “2” and “4” so I can use this information for logging purposes. However, once I filter the DataTable, there will be less rows in the resultant DataTable, so they no longer match the actual Excel Row numbers.

How could I achieve this?

2

Hi @tomato25,

In order to retain the original row number, you would need to store this information somewhere. The simplest way is to add a column in Excel or in your DataTable to keep track of this information.
I believe you can manage in Excel, but for the DataTable you can do something like this
https://social.msdn.microsoft.com/Forums/en-US/c52a2acd-0c15-49de-90fc-7923025d4a7f/adding-an-index-to-a-datatable?forum=csharplanguage

Thanks

1 Like
3

I actually used “Add Data Column” and added the Excel row numbers to the DataTable.
Thank you, this works great.

4

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