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

[Xyleene]

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!!!!

[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:

[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.

[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? 

[apathetichell]

I was worried that might be the case - schema can be hard. Easiest way to do that is probably to re-route to two distinct outputs and use a block until done. If you can send me schema examples I can show you a routing a strategy....

If you want to try yourself basically you can use a generate row (1 row) and append it to a blockuntil done tool and to the two datastreams. You then drop your dummy field in a select tool prior to output.

important - adjust the append fields to allow for 17 or more records in both appends.

important - drop your dummy stream.

This would be in lieu of the Union and your filter tool.

It basically says that you are taking two files sources and creating two excel sheets based upon each region. If you need to do a pivot/aggregation that would be a third sheet/separate process. Right?

[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.

[apathetichell]

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

[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"