Split excel file into multiple files

Help Studio
1

Hello community!

I have scenerio where there are 9 different ids and bot will download .xls file for all the ids,if the excel file is found greater then 500Mb then split that excel file into multiple files into 50000 rows with the headers.
And if my parent file name is File1.xls then my next splitted file name will be File2.xls file3.xls for same id if found greater then 500Mb.

2

Hello,

See the below steps,

1. Download the Excel file.

2. Check the file size - for this you can you File Info activity to the size of the file. If size > 500 AM proceed to split the file.

3. Rad the Excel file,

  • Use the Excel Application Scope activity to open the file.
  • Use the Read Range activity to read data (Read the whole sheet if the row count is unknown).

4. Split Excel into Smaller Files.

  • Use a For Each Row loop to process data in chunks.
  • Create a counter to check the number of rows (50,000).
  • Every time the counter reaches 50,000 create a new file along with the header.

5. Naming Split Files - To achieve this maintain a variable or argument with the file name and use a counter to increment the file name if the original file is named File1.xlsx

3

Hi @Priyesh_Shetty

Can you try like below

This image shows a UiPath workflow designed to read data from an Excel file, split it into chunks of 50,000 rows, and write each chunk to a new Excel file, incrementing the file name for each chunk. (Captioned by AI)
This image shows a UiPath workflow designed to read data from an Excel file, split it into chunks of 50,000 rows, and write each chunk to a new Excel file, incrementing the file name for each chunk. (Captioned by AI)832×791 41.2 KB

dt.AsEnumerable.Chunk(50000).Select(Function(a) a.CopyToDataTable).ToList

Regards,

4

@lrtetala what about file naming if my parent file name is File1.xls after splitting it should be File2.xls File3.xls and so on.

5

@Priyesh_Shetty

Assign int_Count Variable = 2 then it will create files like File2, File3,…

Regards,