Split Excel based on row data rows

I would like to know how I can split an excel document into multiple excel documents with the records split based on number of rows in excel.
For example If I have an excel of 200 records, maximum I can only have 60 records in 1 separated excel, so there will have 200/60 = 3.33, → 4 batches of files.
I have to split it through UIpath and give 4 Excel documents of 60 records each as Output. (The last one is 20 rows)

3 Likes

@Haha

Refer this post it will help you.

Regards,
Mahesh

Thank you but could you explain a little bit more about nskip? Coz I am new to programming and uipath, not quite understanding for the coding.
Also, if I need to seperate the original worksheet to different workbooks(instead of worksheet in your example), then loop through the seperated workbooks for next step like upload them 1 by 1 to somewhere else, how could I do it?

@Haha

I took the post @MAHESH1 linked and tried to add some clarity for you in the attached XAML. Split Excel.zip (30.4 KB)

nSkip in the post was simply a variable name which continued to count the number of original rows to skip. My XAML file uses different variable names but the code works the same.

OriginalRecords.AsEnumerable().Skip(NumberToSkip).Take(RecordsPerFile).CopyToDatatable()

Tells the robot to copy from the OriginalRecords datatable the integer value stored in RecordsPerFile (in your case 60), skipping over the integer value stored in NumberToSkip. Within the while loop the automation keeps track of how many records have been copied with each iteration and increments a Counter variable used to name the output sheets while continuing to accumulate within the NumberToSkip variable how many records have already been copied so that records are never duplicated.

The robot then uses System.IO.Directory.GetFiles to assign the full path of each output file it created to an array of strings and a for each activity to loop through the array of file paths. You could change the write line activity to send the files somewhere else using the full path.

3 Likes

Thanks!!! I combined the solution to my own program and it is working smoothly!!

But I have 1 more question, I use the Output split files as an input and upload them to SAP. (in .xlsx format), it will prompt me an error message:
“The File Format and extension of ‘~SAP{XXXXXX-XXXX-XXXX-XXXX-XXXXXXXxx}’ don’t match. The file could be corrupted or unsafe. Unless you trust its source, don’t open it. Do you want to open it anyway?”

I then tried to open the excel manually and save as .xlsx and upload again, there will have no error message during upload, I guest this is due to the way we write the data to split file. Is there anyway to fix it?
Thanks!

1 Like

What version of excel are you using? When you open the file and save manually are you prompted at all regarding the file format or extension? Is your UiPath.Excel.Activities package updated to the latest version? It sounds like it is being output as a previous excel version (.xls or similar) but the file extension is showing .xlsx and generating this warning (this is pure speculation though).

I don’t have any SAP experience nor access so I can’t help troubleshoot it from that end I’m afraid.

1 Like

Hi, I tried your workflow but it creates an empty excel sometimes.

@Ganesh_A, I’d imagine you have some blank records that are being included in your original records datatable. If you are reading from a range and you are not specifying the range to be read make sure there are no empty rows being included in the data. This process is still working as expected on my machines. If there are still problems, if you’d provide more details as to what has been changed it could help me determine the issue.

@Tyler_Williams
Hi,
It is working fine now. I don’t know what was the issue to get blank excel before.
Thanks you for the response :blush:

Hi Tyler,

Suppose we have to divide the total row count of rows by any no be it(2,3,4,5,6…) and split the main excel based on divided value.
Eg: We have 31 rows and we divide it by 5 we get decimal value after converting to whole no it has to split the file with row count 6+6+6+6+7 with total 5 files,similarly if we 30 rows and divide by 5 it has to split the file with row count 6+6++6+6 with total files.
Both scenarios code needs to work.

Please can you help.