Split Excel based on row data rows


#1

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)


Divide Excel sheet
#2

@Haha

Refer this post it will help you.

Regards,
Mahesh


#3

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?


#4

@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.


#5

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!


#6

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.