Output multiple excel files using a single Excel Template with 2 tabs

Hello!! First I shall start out saying a bit of an Alteryx newbie. I've been searching the community threads to solve this but am going mad!! I'm hoping to get some help building a workflow that takes two inputs with a common "Region" field and exports individual Region files using a single excel template with two tabs.

Please see the attached data samples

1. Input 1 - Active Products

2. Input 2 - Discontinued Products

3. Sample Template

Desired output goal would be:

NAM file - with Active/Discontinued on separate tabs using excel template format

LAM File - with Active/Discontinued on separate tabs using excel template format

EMEA File - with Active/Discontinued on separate tabs using excel template format

APJ File - with Active/Discontinued on separate tabs using excel template format

Any help you can provide would be GREATLY appreciated!!!!

Active_Products.xlsx

Discontinued_Products.xlsx

Sample_Template.xlsx

Input

Output

Batch Macro

Join

Preparation

Accepted answers

Xyleene

OMG - I just fixed it!!!!!!! Updated the formulas and added a Block till done!!!!! Thank you thank you thank you a billion times for sticking with me on this crazy workflow. Happy Friday!!!!!!!

All comments

Adrian_T

Hey @Xyleene,

This should do the trick. I created a column [Output Path] which is used in the Output Data Tool under the "Take File/Table Name from Field" configuration. Remember to select "Change Entire File Path". 🙂

Example output - APJ:

Output to multiple spreadsheets and tabs.yxmd

Xyleene

Thank you, this definitely solves the first part but is there a way to output all of the files with the format found in the Sample template? I'd like the outputs to have the header with filters applied/freeze pane and color coded. The real data is approximately 200K records per file (times 20 - roughly 2 million lines of data). I've tried the table/render tool but it never works.

apathetichell

See the attached. Basically here is how you do it:

1) take the full path of your template file.

2) read it into the blob input.

3) dynamically create a file for each region using blob output.

4) use a block until done to make sure that that has been done first.

5) now union your files together.

6) take the dynamically created filenames.

7) output to specific sheets in your dynamically created files.

blob output by file.yxzp

apathetichell

@Xyleene- can you port my logic to your workflow (or port your transform/process to my workflow) and test it - and then mark my solution correct?

Xyleene

The workflow you outlined was amazing!! I started building the workflow with real time data and realized for the data set I have different headers on both tabs. As seen in the attached screenshot, I tried adding a filter with the same formula pointing to output files but it doesn't seem to work.

Hopefully you can suggest a quick fix?

Work_flow_screenshot.jpg

Xyleene

I think you lost me at "dummy field" and my apologies for being such a newbie. 😂

I've attached the header format with some fake Active/EOS samples. I've also include the current workflow and the template. Luckily the output of the file would be the final format so there will never be additional tabs or pivot tables added. Its just formatted data (lots of it).

Side note: Ideally because of the amount of data that will be contained in these files I would love a .xlsb output, but from my limited experience with Alteryx .xlsb files seem problematic.

active_combined.xlsx

eos_combined.xlsx

Create_Combined_files.yxmd

Active-EOS_Combined_Template3.xlsx

apathetichell

Closer? You may want to do some work on your conversions in the Select tool - but that's another thread/issue.

Create_Combined_files.yxzp

Xyleene

I definitely understand the logic with the outputs now, but when I run the files now I get these results:

1. With output containers off - Beautiful templates but blank

2. With output containers on - error message saying: Unable to open file for write: C:\ - Can't open file: C:\: The system cannot find the path specified. (3)

I've tried adding a block until done to both output streams, turning AMP off and changing the file to .xls but still get the same "write issue"

apathetichell

containers were just for my testing - you can ignore them... Can you confirm that you updated the path in the text input tool for the template? If - so can you share the file results on run? basically it should be creating the templates first - and if it doesn't it can't find a path.

Xyleene

I did update the path and it is creating the templates first! 😀

apathetichell

can you share the file part of your results window - something is off in the location of the files or the naming convention of the files created.

and make sure AMP is off for now.

Xyleene

Imported workflow – came in with 2 errors. Blob output and input were blank. I updated these with the folder location.

Output – just named test

Input – Pointed to template file

Updated template path - ran workflow with AMP off. The result is the expected 20 blank templates.

PS - don't mind all of those "warning". The input data has Date/Number fields that show blank fields with a dash. I'm converting these to a Date/Number field since Alteryx can't have text/num value in the same column

ASEAN USD_DISTRIBUTOR_ACTIVE-EOS_Combined_Data2022_06.xlsx

apathetichell

This is weird. Did you deactivate containers with the output tools? Can you show me the results of the either join tool which bring the dynamic filename to match with the input file via price list name & sales channel? Assuming no changes happen to those fields in between the summarize tool and the join - those should match 1-1.

Xyleene

For the workflow the only things I changed were the Template Name/Directory and filled in the blanks for the Blob Input/output during import. When I open the workflow the output containers are turned off (I didn't touch those).

apathetichell

toggle on the containers!

Xyleene

It creates blank templates but I get the follow error message when you toggle the containers on (tried with AMP on and off):

apathetichell

can you share the file results screen?

Xyleene

Let me know if this is what you want to see:

apathetichell

o.k. - can you attach a browse screen before the output tool and see if the filename matches the files being created in the blob output?

apathetichell

I feel stupid. this needs to be in Template Path - probably fixed it in one version and sent over another... [Template Path]+"|`ALL EOS COMBINED`$A2:AZ500000"

That has to be the value sent to the full path qualifier - and right now the formula tool before both of the output tools are broken. They don't include the template file from the blob output - just the sheet reference.

apathetichell

there were a few issues on this so I'm sending over a new workflow.

Xyleene

Results in folder with AMP off & output containers on or off - Names don't match/no excel files created

Results with AMP on & output containers off - Files create but blank. Same result with AMP on & output containers on.

Hahaha this workflow is sooo strange!

apathetichell

made a few changes and included some notes to help you troubleshoot the process.

Create_Combined_files 6-17.yxzp

Xyleene

Sadly I still can't get this to work 😭

1. Downloaded your file - changed files

2. Updated Template Copier Path

3. Should this formula say Active instead of EOS?

4. Should this formula say: [Template Path]+"|`ALL EOS COMBINED`$A2:AZ500000"

5. Error message when I run (AMP off): Error: Output Data (61): Unable to open file for write: - Can't open file: The system cannot find the path specified. (3)

6. Sigh..

1. 2. 3. .

4. 5.

Xyleene

OMG - I just fixed it!!!!!!! Updated the formulas and added a Block till done!!!!! Thank you thank you thank you a billion times for sticking with me on this crazy workflow. Happy Friday!!!!!!!

apathetichell

Glad to hear. Sometimes we do need to use extra block until dones... Watch out running this workflow with very very small inputs - I wouldn't be surprised if the timing got thrown.

Xyleene

That's what I was thinking too but trust me I'm running it now with real data (2 million records) so that def. won't be an issue. YOU ARE AMAZING and again thank you soooo much for your help. 😘

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703