How to separate/split/ group data from excel on basis of specific row content and store it in separate datatable

Help Studio
1

Hi, I am trying to split / group data in excel on the basis of a specific row content and storing it in a separate data table.
Sharing the example below:

I am having an excel sheet with column names as “BatchID”, “EmployeeName”, “StartDate” , “EndDate” etc.

“BatchID” unique for one single employee, and there can be multiple entries of one employee.
Likewise there is data for N number of employees in one sheet,
The data is already grouped by assigning specific “BatchID” for each row for single employee.
I want to separate this data as a dataset based on “BatchID” and store in separate datatable.
Below is image of the excel sheet data.
Please help on this.

image
image1203×547 25.1 KB

2

Hi,

Hope the following sample helps you.

Sample20200318-1.zip (16.6 KB)

Regards,

1 Like
3

Hi @Yoichi,

Thanks for the solution, i am getting the desired output.
But, the checkbox here for Add Headers need be checked,
the input file which i am getting has column names repetitive in nature, this is restricting me from checking the Add Headers check box.

Do you have something for this.?

4

Hi,

Certainly we cannot use add header option , if column name is duplicated.
So probably we need to indicate the column by index number as r(0) instead of r(“BatchID”)

Regards,

1 Like