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

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.

Hi,

Hope the following sample helps you.

Sample20200318-1.zip (16.6 KB)

Regards,

1 Like

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.?

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