Dynamically Split records based on a unique field value and export to excel

[vshekharrc]

Hi I have a simple Excel file attached here. it has two fields ID(unique) and values. Values is simply some column with some arbitrary values. There are 53 records. I want to split this into different sets and export into an excel file with each set having at most 15 records; this way the number of sets changes based whenever the number of records changes. So my Set 1 will get 15 records, set 2 will get 15 records, set 3 will get 15 and set 4 will get 8 records. I want this to be done dynamically so that if tomorrow number of records changes, workflow should automatically split it and export into excel files as test_set1.xlsx, test_set2.xlsx, test_set3.xlsx and test_set4.xlsx. The number "15" which is the max number of records in each set mentioned above is constant.

Can somebody please help me out here to get this solved. Kindly share the workflow, so that I can understand the flow and that would be very helpful

Regards

Varun

[JulioMO]

Hi @vshekharrc 

Here is a workflow that performs what you want. You will have to introduce the exact file path you want to save the file to in the formula tool and selected the destination path in the output tool in order to make it work properly

Please note the configuration of the output tool in the configuration window (the take File/Table Name From Field is selected and configured properly).

Hope it helps, 

Regards

[vshekharrc]

Hi @JulioMO

Thank you very much for the quick response. I ran the workflow to run into the following error:

"Unable to Open archive for zipping: C:\test_set1.xlsx Can't open file: C:\test_set1.xlsx: Access is denied. (5)"

In the formula tool, Under the Name field, I put the path as C:\

Also "Under the write to file/database" in the output tool configuration window I have mentioned

\Name

I get that above error. Am I doing something wrong in the output stage? Kindly help.

Regards

Varun

[JulioMO · Accepted solution]

Hi @vshekharrc 

In the formula tool make sure the path is correct and you do not forget to add the backslash symbol: 

"C:\Users\xx\Documents\Folder\"+[test_set]+".xlsx|sheet1"

In the output tool, under the "Write to File or Database", you will have to specify the path again. It does not matter the name you give to this file, since you are going to change it for all the test_set you will have. However, it is important that you keep the same sheet name (sheet1 in this case). If we keep the same path, it should look like this: C:\Users\xx\Documents\Folder\Output.xlsx|||Sheet1

Finally, in the output tool, the box "Take File/Table Name From Field" should be selected, choose "Change Entire File Path" and Select your Name Column you have just created. 

Let me know if this helps you.

Regards

[vshekharrc]

 Hi JulioMo,

This is working as I wanted! Initially I was trying to input files to my C drive which also contains program files related to the tools and hence I was not getting the Access to output the file and hence the error. Also as you had mentioned, I put the same path name under the "Write to File or Database" and it is working now!

Thank you very much for sharing your time and knowledge!

Regards

Varun Shekhar