Split a excel sheet in the based of specfic row (name)

i have a excel sheet and i want to split the excel sheet by specific row…how can i do that??
suposse i have a excel sheet
sl.no name location time
1 sde ban 30
2 wer mum 40

sl.no name location time
1 ert ban 50
2 mrt mum 60

input and output in same excel sheet…i want to split as form of datatable input and output?
How can i do that???

Let’s take like we have a excel with 1000 rows
—now use a excel application scope and pass the file path as input
—inside the scope use a read range activity and get the output with a variable of type datatable named dt
—now use a assign activity like this
input_dt = dt.AsEnumerable().Take(no.of rows).CopyToDatatable()

And for the second half datatable use expression like this
output_dt = dt.Asenumerable().Skip(no.of records).CopyToDatatable()

Where both input_dt and output_dt is a variable of type Datatable with default value as New System.Data.Datatable defined in the variable panel

Cheers @suneel070

1 Like

Hi @Palaniyappan,

This solution assumes that we have fix number of rows for the input_dt?
For unknown no. of rows, would you have an alternative way Data Linq?

From my side, I usually use the Lookup Data Table activity to find the position/row index of the row that contains the ColumnName(in this case B-output) and from there get the next set of dt.

1 Like