Existing excel rows to datatable

Help Activities
, ,
1

I have an scenario where in an excel sheet i have 12 rows out of which 6 rows should be pushed in a datatable 1 and other 6 rows in a datatable 2 so on that if excel has 36 rows I should get 6 datatables. What will be the logic/workflow ? Or do we need to invoke any vb code?

2

Hi @Pogboom

We can’t create the variables dynamically as you said datatable1, datatable2 and so on.
But we can segregate the 6 rows in the one sheet and another six rows in another sheet.
If you are okay with the above statement then I’ll provide you the workflow.

Hope it helps!!

3

@Pogboom

you can you use this query

dt1.AsEnumerable.Chunk(6).Select(Function(a) a.copytodatatable).tolist

output is list of datatables

4

dt2.AsEnumerable.Chunk(6).Select(Function(chunk) chunk.CopyToDataTable).ToList

  1. dt2.AsEnumerable: Converts a DataTable (dt2) into an enumerable sequence of DataRow objects.
  2. Chunk(6): Groups the DataRow objects into chunks of size 6. The Chunk method is assumed to be a custom extension method that is not part of the standard LINQ methods.
  3. Select(Function(chunk) chunk.CopyToDataTable): For each chunk of DataRow objects, converts the chunk into a DataTable using the CopyToDataTable method. CopyToDataTable is a standard method provided by DataTableExtensions class in System.Data namespace.
  4. ToList(): Converts the resulting sequence of DataTables into a List.
5

Yes please provide the workflow

6

@Pogboom

refer this

Chunkdatatatables.zip (10.6 KB)

7

Okay @Pogboom

I have done the workflow for your query by using LINQ Expressions and Workbook activities.
Check the below workflow and output attached file for better understanding,

_2024
_2024884×1904 122 KB

Workflow -
2024.xaml (18.2 KB)

Output file -
Dt__2.xlsx (21.0 KB)

Hope it helps!!