community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

Learn More
SOLVED

Outputting to multiple Excel files with multiple sheets

Meteor

I am new to Alteryx and trying to generate an Excel output file for each entity below with two sheets.  The sheet names will correspond to Schedules 1 and 2.   I have an output data generated in the workflow as shown below.  I have also attached a sample data file to the post.    

 

The file path should be c:\Cycle18\CountryNameFrombelow\

The file name should be EntityCodefromBelow.xlsx 

Sheet names should be Schedulenumberfrombelow i.e. 1 or 2  

 

 

EntityScheduleAccountAmountCountryPReparerEmail
ABC11005000BelgiumJoan@abc.com
ABC120010000BelgiumJoan@abc.com
ABC1Total 15000BelgiumJoan@abc.com
ABC240020000BelgiumJoan@abc.com
ABC2Total 20000BelgiumJoan@abc.com
XYZ110030000SwitzerlandAnn@xyz.com
XYZ1Total 30000SwitzerlandAnn@xyz.com
XYZ230040000SwitzerlandAnn@xyz.com
XYZ2Total 100000SwitzerlandAnn@xyz.com

 

Below is a sample of the two sheets:  

 

 

Each sheet should have a header that contains the entity code, Schedule and prepare e-mail.  

 

Sheet named 1

EntityABC
Schedule1
Preparer e-mailJoan@abc.com
  
AccountAmount
1005000
20010000
Total 15000

 

 

 

Sheet Named 2

EntityABC
Schedule2
Preparer e-mailJoan@abc.com
  
AccountAmount
30025000
40020000
Total 45000

 

Any ideas that will put me in the right track to generate the output Excel files efficiently are greatly appreciated.  

 

Purpose

 

Alteryx Certified Partner
Alteryx Certified Partner

Hi @Purpose 

 

I don't understand where '300' value of account comes from in Sheet 2:

 

Sheet Named 2

EntityABC
Schedule2
Preparer e-mailJoan@abc.com
  
AccountAmount
30025000
40020000
Total 45000

 

 

Cheers,

Alteryx Certified Partner
Alteryx Certified Partner

@Purpose 

 

Here's a solution that I came up with from what I understood:

IterativeExcel.PNG

 

This is an Iterative Macro.

 

- Takes Entity Field, Schedule and E-mail by summarizing

- Gets the first 1 - so it runs one every iteration

- Join Tool is used to get Account and Amount to that Entity/Schedule pair.

  1) J side gets these values. I also use Multi-Field formula tool to get field names to enter the Union Tool

  2) R side will be what is going to feed Input's next iteration. 

- Transpose the first 1 summarized row, so I can get the header information as you asked in your model (row pairs)

- I also use a Text Input with 0 values that I transform to Null so I can insert an empty row in the middle (as your template looks like)

- Union Tool to put everything together. I union by position and configured to the right order

- Create FilePath field with Formula Tool with Entity and Schedule. Folder Browse Interface tool is there  so you can choose whatever folder would be the best for you.

- Append FilePath field to dataset

- Dynamic Rename to get Entity and Entity's name as field names, so they come first.

- Output Tool so every iteration is going to be generated a new sheet with a different FilePath (Take File/Table Name Field option is checked).

 

Here is the package with macro and workflow appended.

 

Cheers,

Meteor

Thanks a lot Thableaus for your response. 

 

Apologies, You are right that there is a mistake in the sample data set output I provided.  I am going through your suggestions and will report back.  

 

Purpose

Meteor

Thableaus,

The workflow and macro worked exceptionally well.  Do you know how I can add country as a sub folder?   

In other words, the excel output files that belong to Belgium entities should be saved in the Belgium file folder.   

 

Also, I got an error when I reran the workflow, which says that the sheet already exists.  This is, I believe, caused by the output files saved from the previous run.  Is it possible to overwrite previous files? 

 

Thanks a lot.  

Purpose  

Alteryx Certified Partner
Alteryx Certified Partner

Hi @Purpose 

 

Ok, I racked my brains this afternoon to set up the solution for you.

 

I used Run Command Tool to create sub-directory with Country Name (also had to create a formula) and modified the Output Tool to Overwrite Sheet.

 

SolutionPurpose.PNG

 

(You can see the macro got even bigger)

 

The Run Command Tool will create a bat file that creates the folder. So this bat file will be in the folder where you're creating your sheets. You can create another bat file to delete it after run, if you want (but that's not the focus now).

 

Solution appended. I hope it suits you. Have a nice week.

 

Cheers,

Meteor

Thableaus,

 

This worked as intended and I tagged the issue as resolved. I learned quite a bit from your workflow.  Thanks a lot.  

 

I had to fix one issue where country name contained a space e.g. South Africa.  Run command created South and Africa folders i.e. two folder in this instance.  I inserted a double quote before and after the country field to make this work ("South Africa").  Then the excel output file resulted in an error since it did not find folder that contains "South Africa" .  Thus to fix the second issue, I removed the double quotes using FindReplace tool before the excel output file was created. 

 

It took about 90 seconds to finish a small sample file, which is a performance problem given I have a large volume of entities.  To improve performance, I am thinking about creating country sub-folders in one mass and separate it from the iterative macro since I know the list of the countries from my input file.  Is it possible to pass multiple sub folders to Run command?    

    

I am also thinking using the table and render tools to create an output file for single entity as part of the iterative macro.  Couple of things I learned from my data providers is that the output excel files must have the same number of sheets and subtotal rows formatted to bold font. Please let me know if you have If I am not on the right track or if you have better ideas for improving the performance. 

 

Purpose.  

 

Labels