Split into no.of excels based on the count of rows in the input excel

Help Studio
1

Hello all,

I have to split into number of excels based on the count of no.of rows in the input.
This is the input excel with 5 rows

ERP_Ready_Data.xlsx (11.1 KB)

I need to get output which has 5 excels , accordingly with each row data in the each excel .

Could anyone pls suggest how to resolve it.

Thanks.

2

Hi @sushmithaelluru

Try this

  1. Use Read Range to read the input Excel into a DataTable
  2. Use a For Each Row in DataTable loop.
  3. Inside the loop, create a new DataTable with the same schema using dt.Clone().
  4. Import the current row into the new DataTable using dtNew.ImportRow(row).
  5. Use Write Range to write dtNew to a new Excel file. Generate the file name dynamically, for example:
    • Output_1.xlsx
    • Output_2.xlsx
    • Output_3.xlsx

This will create one Excel file per row. So if the input contains 5 rows, the workflow will generate 5 Excel files, each containing a single row (and the headers).

If you’re using the Modern Excel activities you can achieve the same logic with Use Excel File → Read Range → For Each Excel Row/DataTable Row → Write Range while dynamically creating the output file name inside the loop.

If solution works for you please mark as solution

Thanks

3

You can achieve this in UiPath with a simple loop.

Logic

  1. Read the Excel into a DataTable.
  2. Loop through each row.
  3. Create a new DataTable with the same schema.
  4. Add the current row to the new DataTable.
  5. Write the new DataTable to a separate Excel file.
4

@sushmithaelluru

Refer this solution

and this one as well

5

Hi @sushmithaelluru,

You can try this apparoach:

  1. Use Read Range activity to read the excel file into a DataTable “dtInput”.
  2. Use a For Each Row in DataTable activity and loop through each row in “dtInput”.
  3. Inside the loop
  • Create a new DataTable with the same structure: dtNew = dtInput.Clone()
  • Add the current row: dtNew.ImportRow(row)
  • Write the new DataTable to a new Excel file using Write Range activity.
  1. Generate unique file names
  • Use a counter variable “fileIndex” initialized to “1”.
  • File name: “Output_” + fileIndex.ToString + “.xlsx”
  • Increment the counter after writing: fileIndex = fileIndex + 1

You can also refer to this YouTube Video - Split Excel File by Category Using UiPath | Automate Excel File Separation with RPA

Happy Automation!

6

HI @nishi_jain , thankyou so much for the reply and solution.

Can you explain clearly about the 4th point.

7

I am getting this error for the 4th step ,

image
image1447×919 88.5 KB

8

@sushmithaelluru
Import the current row**
Do NOT use Add Data Row.

Instead, use Invoke Method.

Configure it like this:

  • TargetObject = finaldt
  • MethodName = ImportRow

Add a parameter
Direction
Type
Value
In
System.Data.DataRow
row

This adds the current row into finaldt.
After that use write range

Hope it help now

9

@sushmithaelluru if above solution works for you please mark as solution so it’s help other to
Thank’s

10

Thanks much, it worked. But I got only one excel. It needs to generate 5 excels.

11

@sushmithaelluru
Try this
Read Range
↓
counter = 1
↓
For Each Row in DataTable
Assign finaldt = Erp_Ready_Dt.Clone()
Invoke Method (ImportRow)
Write Range ← MUST be inside the loop
Assign counter = counter + 1
End For

12

It didn’t work , where I didnt get 5 output excels.

image
image1021×892 47.8 KB

image
image1041×886 56.3 KB

13

Hi @sushmithaelluru,

Your Excel file is being generated successfully, but it is overwritten for each row because the output file name is always Final.xlsx. Since the file name remains the same, the Write Range activity replaces the existing file whenever it finds a file with that name in the target folder. To avoid this, use a unique file name for each iteration (for example, by appending the row number, timestamp, or another unique identifier).

Please have a different file name for every row as mentioned here - Split into no.of excels based on the count of rows in the input excel - #5 by Prashanth_D

14

Thanks @Prashanth_D , it worked.