Increment by 50 rows and create new excel for each

Help Studio
, ,
1

Hi,

This is regarding splitting excel workbook into different workbooks depending on row count reaching 50 rows.

Eg . A workbook has 100 rows
Then, Excel would split the 100 rows into 2 workbooks each having 50 rows.
The trick is that it can be 2 rows to 100,000 rows workbook. It is not constant. So, im trying to find a way to count the number of rows and then a way to open a new excel and paste the rows.

Request ideas and suggestion to implement this scenario in uipath please.

Thanks
Sara

2

Hi, see this code for the idea of how to do it:

private static List<DataTable> SplitTable(DataTable originalTable, int batchSize)
    {
        List<DataTable> tables = new List<DataTable>();
        int i = 0;
        int j = 1;
        DataTable newDt = originalTable.Clone();
        newDt.TableName = "Table_" + j;
        newDt.Clear();
        foreach (DataRow row in originalTable.Rows)
        {
            DataRow newRow = newDt.NewRow();
            newRow.ItemArray = row.ItemArray;
            newDt.Rows.Add(newRow);
            i++;
            if (i == batchSize)
            {
                tables.Add(newDt);
                j++;
                newDt = originalTable.Clone();
                newDt.TableName = "Table_" + j;
                newDt.Clear();
                i = 0;
            }



        }
        if (newDt.Rows.Count > 0)
        {
            tables.Add(newDt);
            j++;
            newDt = originalTable.Clone();
            newDt.TableName = "Table_" + j;
            newDt.Clear();

        }
        return tables;
    }
3

Thank you for your reply. Is this a code I can put in an activity or is this a macro in excel?
Thanks

4

you can put the code inside the funcion in an Invoke Code (C#) activity with arguments:
OUT name: SplitTable=> List(Of DataTable)
IN name originalTable => DataTable
IN name batchSize => Integer
Try it and if you have too much trouble i help you out.

1 Like