Split output into multiple Excel files to different folders

[YeahMan]

Hi Community,

I'm trying to split my output into multiple Excel and save to multiple different directories/folders based on fields in my data. My data looks like:

I would like each Program to be its own Excel file AND I would like to save the output to a folder that matches the School. So all the data related to 'Acting for Film and Television - P0198' would be written to

...\Schools\Media\Programs\Acting for Film and Television - P0198.xlsx.

Similarly, 'Culinary Management - P0435' data would be saved to ...\Schools\Food & Wine Sciences\Programs\Culinary Management - P0435.xlsx.

The folders for the Schools already exist; I just need to separate the data, give each Excel workbook the appropriate name and dump it in the right place by matching on the School. I've looked at a bunch of the posted solutions, but I am not finding anything that combines the 2 tasks. BTW there are 137 programs so using filters would be a bit daunting.

Thanks,

--sue

[JosephSerpis · Accepted solution]

Hi @YeahMan this article shows you how to tackle this and has an example workflow. Esentially you are going to need build the filepath using a formula tool and the fields you identified. Then using the Output Data Tool configuration options to use/change the filepath with a field should output the data in the right folders and be separated correctly.

[messi007 · Accepted solution]

@YeahMan,

Please see below:

you have to create the path for every type and then use it in the output like above.

Attached the workflow,

Regards

[YeahMan]

Thanks @messi007! This did the trick and is a simple solution. 

I went one step further and created a new column:

"C:\Temp\Schools\" + [School] + "\Programs\" + [Program]+".xlsx|||DATA"

then used it in the "Field Containing File Name or Part of the File Name" parameter. It avoids a long, nasty IF statement as I have 13 different Schools :).

Thanks again,

--sue

[messi007]

@YeahMan,

Good, happy to help 🙂

Best Regards,

[YeahMan]

Hi again @messi007 ,

Now that you know what I am trying to do, do you have any advice on how to format the output into an Excel table? My only other option would be to open the 137 Excel files and manually insert tables. It doesn't even have to be a table, but something a bit prettier than just a dump of data. I've looked at using an Excel template, but I don't think that would work when there are more than one output file.

[messi007]

@YeahMan,

You can use an Excel template then inject data on it.

You have to check keep formatting on the Excel output:

Hope that helps 🙂

Regards,

[YeahMan]

Hmm, this doesn't seem to be working. I've set up a named range in test.xlsx and used the following settings:

But it is still outputting as a data dump.

The other option I've tried is using a table tool and a render tool, which gives me prettier output, but I lose the ability to save it to different folders.

Thanks,

--sue

[YeahMan]

Lol, @JosephSerpis  turns out this is what I did after building out from the reply from @messi007 !

--sue

[messi007]

@YeahMan,

In this case use the basic table in order to do the formatting in Alteryx then Render Tool to generate the output.

Take a look to this link: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Render-tool-as-output-with-multiple-ex... 

Best Regards,