Remove some data rows on top a datatable

Help
, ,
1

I have a data table and need to delete some row on the top of table, I used for each row and do while statement control to count index and total row of data table and then use remove data row activity to remove row 0 until index = number I expect. But loop control still has error.
Then, I referenced the following solution of @ClaytonM but still error because about “header1” variable, I don’t understand why has this var on the statement.

DataTable_Variable.AsEnumerable.Skip( DataTable_Variable.Rows.IndexOf( DataTable_Variable.AsEnumerable.Where(Function(r) r(0).ToString.Trim.Equals(header1) ).ToArray(0) ) ).CopyToDataTable

Anyone can help me, plz? Thanks so much

1 Like
2

@ngocvk,

In this query they are removing the rows based on the value in the row(0) cell value equals to the header1. They used the header1 as some string which is equal to the row(0) cell and then they are removing that row.

So you need to declare or make the condition as per your need

3

Hi @ngocvk
I tried at my end and i figure out that you didnt declare the header1 i guess?
What is the value of your header1 did you declare it ?

cheers :smiley:

Happy learning :smiley:

1 Like
4

Hi.

I can clarify that solution, and improve on it, cause there is a flaw which will give you an error if the table does not contain a row with that value.

Let’s assume you have a key word to identify the row you want to delete.
headerKey = "Invoice Number"
the variable can be initialized in an Assign or in the Default value when you create it.

Then, using that variable, you can find the row using .net and assign that to an Array of DataRows
Assign: headerRows = datatableVariable.AsEnumerable.Where(Function(r) r(0).ToString.Trim.ToUpper = headerKey.Trim.ToUpper).ToArray

Then, you need to check to make sure you found a match, otherwise it will throw an error. And, use the indexOf() the last item in the array of rows to skip to, then .CopyToDataTable()

If headerRows.Count > 0
Then,
    Assign: datatableVariable = datatableVariable.AsEnumerable.Skip( datatableVariable.Rows.IndexOf( headerRows(headerRows.Count-1) )+1 ).CopyToDataTable

If you have any errors, please post them, and we can easily identify the problem.

The alternative solution to this, is to copy the table to a new variable, then loop through the original table, but delete from the new variable, until you get to the header key word.

So, that would look like this:

newDatatable = datatableVariable.AsEnumerable.CopyToDataTable

For each row In datatableVariable.AsEnumerable //TypeArgument DataRow. Use Output property for index
    Delete row //using index of ForEach, use: newDatatable.Rows.IndexOf(index)
    If row(0).ToString.Trim.ToUpper = headerKey.Trim.ToUpper
       Break activity

Regards!