Segregate file with equal/almost same quantity

[aqiffm]

I have a list of excel. however, i want to divide number of rows equally/almost similar based on X number of bots and write range to an excel

For example:
List contains 64 rows.
I have 3 robots.
BOT1.xlsx has 21 rows
BOT2.xlsx has 21 rows
BOT3.xlsx has 22 rows

How can I do this to make it dynamic every time I change the number of bots?

[ppr (Peter Preuss)]

@aqiffm

64

we can decrease the complexity when the following would be ok for you
22 rows - Bot1
22 rows - Bot2
20 rows - Bot3

[rlgandu (Rajyalakshmi Gandu)]

@aqiffm

totalRows = dtData.Rows.Count
numberOfBots = 3  ' Set this dynamically based on input
rowsPerBot = Math.Ceiling(totalRows / numberOfBots)

[ppr (Peter Preuss)]

@ppr

we can decrease the complexity when the following would be ok for you

dtData = your DataTableVar
BotCount = 3
RowCount = DataTableVar.Rows.Count

Assign Activity:
TableList | List(Of DataTable) =

dtData.AsEnumerable.Chunk(CInt(Math.Ceiling(RowCount / BotCount))).Select(function (x) x.CopytoDataTable).toList

Then loop over TableList and writethe looped DataTable back to Excel

[br1117jonnala]

@aqiffm

Read Data where Output is ‘fullDataTable’

Assign :

• numberOfBots = 3

• totalRows = fullDataTable.Rows.Count

• rowsPerBot = Math.Ceiling(totalRows / numberOfBots)

• For Each i In Enumerable.Range(1, numberOfBots)
  Assign

  • startRow = (i - 1) * rowsPerBot
  • Assign
  • splitDataTable = fullDataTable.Clone()

  For Each j In Enumerable.Range(startRow, Math.Min(rowsPerBot, totalRows - startRow))
  Assign
  - splitDataTable.ImportRow(fullDataTable.Rows(j))

  Excel Application Scope

  • File Path: $“path_to_output_split_{i}.xlsx”

  Write Range

  • SheetName: “Sheet1”
  • DataTable: splitDataTable

[aqiffm]

This one makes me learn something new. Chunk is a new method that i learn so that i do not need to initialize data table one by one. TQ!