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

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

test.xlsx

Connectors

Common Use Cases

Dynamic Processing

Preparation

AMP Engine

AMP-Engine

Accepted answers

JulioMO

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

All comments

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

Workflow.yxmd

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335