Hi all,
Can someone help me creating several countries excel files from 1 template?
I have 2 inputs in Alteryx.
Those countries are divided in aggregations, such as “Benelux”, “Nordics”, etc..
I have a template with two charts and tables that are populated by the additional two tabs in the template file. My goal is that Alteryx outputs populates “Economic Data” and “Sales” tabs with the data for each country which would automatically populate the charts and tables.
In addition I need it to save the file as “Analysis_country xxxx” in the aggregation folder. The folder structure is as the example below:
So, from the template file I need to create as many files as countries in the input files (Economic and sales) and save them in the correct aggregation.
Is this possible?
Thank you very much,
Guilherme Neves
Solved! Go to Solution.
vHi Guilherme,
Check out this solution - https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Copy-template-before-running-workflow/...
I think it will accomplish the difficult part of what you're trying to do which is create the "template" that you want to slide your data in to.
The attached workflow copies the template file into the Aggregation folders with the correct name.
The next bit is writing the data into each file to the Sales and Economy Sheets. I'll have a play with that next. I propose to create a range in the 2 sheets in the template file and then write to those ranges.
Sorry, there is a bug in the previous workflow I sent which meant the files were not copied to the correct folders. That's now fixed in the attached. It's also now writing the data to the Sales and Economic Data Sheets.
It's still not quite right, so needs a bit more investigation.
Hi David,
I used the workflow that you suggested but it doesn't run the "Run Command" tool. I get an error message:
Any idea way?
Been trying to figure it out but couldn't.
Thanks,
Guilherme Neves
So the error you're seeing is an error the Run Command tool is encountering when executing the batch file, i.e the DOS command it's trying to execute returns an error.
I should have provided a bit more information as to what the workflow is actually doing and what assumptions are made. And there's actually another minor bug that I noticed this morning, so sorry about that. I'll explain.
The concept I'm using is to create DOS commands to copy the template file into all the aggregation folders and give them the correct names. The summarize tool creates a list of aggregations and countries in each aggregation.
Assumption 1: The folders for the Aggregations already exist in the Sheets folder. Also the files Economic, Sales and Template are in the same folder as the Sheets folder, like this:
Sheets Folder
The workflow picks up wherever all these files and folders are and works from there.
No here's the additional bug I found. The sequence of Dos commands should be:
1. Delete all existing files in the Aggregation folders.
2. Copy clean copies of the Template file to relevant folders with correct name
However, I had both actions in the same step, so it deleted all existing files every time, which means only the last file written was kept. This is now fixed so the sequence of DOS commands for the data provided is:
So the error you're seeing is that these DOS commands in your case are not right, most likely because the Aggregation folders don't exist.
The workflow is designed to pick up the folder path from where the Sales.xlsx file is stored. If you create the Aggregation folders and make sure your files are in the right place as shown above, the attached should correctly create copies of the files in the right folders and write the data to the Sales and Economic Data sheets in the relevant files.
There is currently an additional problem in that it seems to mess up the rest of the Excel file a little, but I'm still looking at that.
Please see new workflow attached.
So the error you're seeing is an error the Run Command tool is encountering when executing the batch file, i.e the DOS command it's trying to execute returns an error.
I should have provided a bit more information as to what the workflow is actually doing and what assumptions are made. And there's actually another minor bug that I noticed this morning, so sorry about that. I'll explain.
The concept I'm using is to create DOS commands to copy the template file into all the aggregation folders and give them the correct names. The summarize tool creates a list of aggregations and countries in each aggregation.
Assumption 1: The folders for the Aggregations already exist in the Sheets folder. Also the files Economic, Sales and Template are in the same folder as the Sheets folder, like this:
Sheets Folder
The workflow picks up wherever all these files and folders are and works from there.
No here's the additional bug I found. The sequence of Dos commands should be:
1. Delete all existing files in the Aggregation folders.
2. Copy clean copies of the Template file to relevant folders with correct name
However, I had both actions in the same step, so it deleted all existing files every time, which means only the last file written was kept. This is now fixed so the sequence of DOS commands for the data provided is:
So the error you're seeing is that these DOS commands in your case are not right, most likely because the Aggregation folders don't exist.
The workflow is designed to pick up the folder path from where the Sales file is stored. If you create the Aggregation folders and make sure your files are in the right place as shown above, the attached should correctly create copies of the files in the right folders and write the data to the Sales and Economic Data sheets in the relevant files.
There is currently an additional problem in that it seems to mess up the rest of the Excel file a little, but I'm still looking at that.
Please see new workflow attached.
My reply post keeps getting rejected, hopefully they don't all come through at once.
So the error you're seeing is when the Run Command tool is trying to execute the DOS commands and they fail. Here's what it's trying to do:
1. Delete all existing files in the Aggregation folders.
2. Copy clean copies of the Template file to relevant folders with correct name
This assumes that the Aggregation folders already exist inside the Sheets folder, like this. Economic, Sales and Template should also be in the same folder as the Sheets folder.
Sheets Folder
I'm guessing that the Aggregation folders don't exist in the Sheets folder, or some files are not in the right place, which is making it fail in your case.
The workflow is designed to pick up the folder path from where the Sales and Economic files are stored. If you create the Aggregation folders and make sure your files are in the right place as shown above, the attached should correctly create copies of the files in the right folders and write the data to the Sales and Economic Data sheets in the relevant files.
Please use the new version attached to this post.
There is currently an additional problem in that it seems to mess up the rest of the Excel file a little, but I'm still looking at that.
Right, one further development.
Using Overwrite Sheet (Drop) in the Output Data tool seems to corrupt the Excel file, so I decided to try creating a range inside both the Sales and Economic Data sheets and write to those ranges instead.
The template file now has 2 ranges defined: salesdata and econimicdata and the output data tools are writing to them instead with the option Append to existing sheet.
This stops the Excel file being corrupt, but your Analysis dashboard is still broken.
In order to run this latest workflow, you'll have to replace the Template.xlsx file with the one I'm attaching.
I'm also attaching a sample output file.
Now it is working!!
It was the folder structure that wasn't the same.
Thank you very much!