Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Several Output Excel files keeping the same single template

Guilherme91
6 - Meteoroid

Hi all,

 

Can someone help me creating several countries excel files from 1 template?

 

I have 2 inputs in Alteryx.

  • “Economic” – Economic data for several countries
  • “Sales” – Sales data for several cities in the same countries

 

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:

 

Guilherme91_0-1580919567721.png

Guilherme91_1-1580919578193.png

 

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

10 REPLIES 10
Blake
12 - Quasar

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. 

DavidP
17 - Castor
17 - Castor

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.

 

Untitled.png

DavidP
17 - Castor
17 - Castor

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.

 

Untitled.png

Guilherme91
6 - Meteoroid

Hi David,

 

I used the workflow that you suggested but it doesn't run the "Run Command" tool. I get an error message:

 

Guilherme91_0-1580990174725.png

 

Any idea way?

 

Been trying to figure it out but couldn't.

 

Thanks,

Guilherme Neves

DavidP
17 - Castor
17 - Castor

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:

 

folders1.png                                           Sheets Folderfolders2.png

 

 

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:

 

Untitled2.png

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.

 

Untitled.png

DavidP
17 - Castor
17 - Castor

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:

 

folders1.png                                           Sheets Folderfolders2.png

 

 

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:

 

Untitled2.png

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.

 

Untitled.png

DavidP
17 - Castor
17 - Castor

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.

 

folders1.png                                           Sheets Folderfolders2.png

 

 

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.

 

 

Untitled.png

DavidP
17 - Castor
17 - Castor

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.

Guilherme91
6 - Meteoroid

Now it is working!!

 

It was the folder structure that wasn't the same.

 

Thank you very much!

Labels