How to extract Rows from the middle of a datatable while extracting the names of the columns?

So I want to extract some rows from a excel starting from the middle of the excel and then do some alteration on them using for each row. My question is how can I extract rows from the middle of a DataTable while still extracting the columns Names?

2 Likes

Hi
Yah of course that’s possible
—While using read range activity we can mention the range accordingly
—If there are 20 rows and if we want to start from 10 th row
—Then in the range mention as “A10” and kindly make sure that we have disabled the ADD HEADERS property in the property panel of read range activity so that the first row that A10 will not be considered as header
— and to access each column we can mention them with column index like 0 for first column and 1 for second column inside the for each row loop like this row(0).Tostring and row(1).ToString and so on

Hope this would help you
Kindly try this and let know for any queries or clarification
Cheers @Nicu_Iuonas

Hi Nicu

you can read the entire table into range and use the below expression which will help you to take only the required rows.

you have to pass the number of rows u have to skip, for example , if u want ur datatable to have only rows starting from 15 , then u can pass count as 14, it ll skip the first 14 and will have the remaining

Assign Dt1 = dt1.asEnumerable.skip(number of rows u want to skip from start). copytodatatable

by this way , you can have the column names(headers) and only the required rows as well :slight_smile:

hope this helps…!

@Nicu_Iuonas

Please follow the steps:-

  • Read Range the datatable (dtReadData)
  • assign the datatable to another datatable variable (lets say dtNew)
  • Use clear datatable on the dtNew (you will get a dtNew datatable with only headers)
  • use for each row in dtReadData and if the certain condition matches then add the datarow to dtNew.

This way you get the newly filtered datatable by retaining your headers.

Thanks and Regards,
@hacky

I had a similar case where I had to resume reading rows in the middle of a column but needed to maintain the column header to rename and assign as another variable used in a later activity. I used the following to accomplish this:
Note: Column Header is e.g. “Legal First Name”

  1. Read Range: entire range (e.g. “A1:A50”) with AddHeaders checked > Output DataTable = dtOld
  2. Assign: dt New=dtOld.AsEnumerable.Skip(# of rows to skip).CopyToDataTable
  3. For Each Row: ForEach row in dtNew
  4. Assign (for variable to use in later activities & variables where we are calling this “Name” instead of “Legal First Name” mentioned in the Note above): Name=row.item(“Legal First Name”).ToString

Variables list:
dtOld
dtNew
Name