How to speed up for each rows process in a Datatable?

[tejas.ma]

Hey everyone i have a csv file that I read as a datatable to perform operations on and after each row i mark another column as “finished” once the process is completed. Later when i reopen the csv file i have to read each row to check if it has the “finished” tag and then run it from where it is blank again. Is there a faster way to do this process?

[postwick (Paul)]

After reading the file, use Filter Data Table to remove the rows that have “finished” in that column. Then just run your loop with the remaining data.

[rajneesh94 (Rajneesh Khare)]

Hi @tejas.ma,

Use LINQ to filter your DataTable as it’s processing speed is very fast. To filter your DataTable to contain only rows that does not have “finished” in them, you can use this statement in an assign activity:

dt_YourDT = dt_YourDT.AsEnumerable.Where(Function(row) Not row.ItemArray.Contains("finished")).CopyToDataTable

Cheers

[tejas.ma]

Thanks for the reply!

[tejas.ma]

Thanks for the response!

[tejas.ma]

Also on an unrelated note, would it be possible for me to find the last row containing data? Or a single column in the last row of a datatable?

[rikulsilva (Henrique Lima)]

Yes, there is some options

But you can do it using this

row_Data = dt_YourDT.AsEnumerable.Last()

row_Data is of DataRow type

If you want a specific last row based on column you can use

dt_YourDT.AsEnumerable.Where(Function(row) Not String.isNullOrEmpty(row.Item(“desire column”).ToString).Last()

[rajneesh94 (Rajneesh Khare)]

Yes @tejas.ma,

To retrieve the last row with data you can query:

dt_YourDT.AsEnumerable.Last

To retrieve a column value in the last row you can query:

dt_YourDT.AsEnumerable.Select(Function(row) row("ColumnName")).Last

Cheers

[jack.chan]

@tejas.ma when you first read your datatable, add a column called “RowNumber” (Type integer)
then using for each rows activity, assign values to RowNumber column

so now datatable looks like this

then filter your datatable where Status != finished

now, you are able to process just the rows where status!= finished, and also update the original datatable by doing this
dt.Rows(cint(CurrentRow("RowNumber").toString))("Status") = "finished"

finally before you write dt back to excel, use remove data column activity to remove RowNumber column

[postwick (Paul)]

LINQ isn’t necessarily faster than For Each, that’s a myth. LINQ is still looping. It really depends what you’re doing in the loop.