topic Output & duplicate template file in Alteryx Designer Desktop Discussions

Output & duplicate template file

Hakimipous — Thu, 24 May 2018 04:35:58 GMT

Hello

Here is the situation I'm dealing with :

I have a YXDB file with the informations from 100 employee.

I would like to output them into a template file (excel file) within a sheet called Alteryx. But in a way that duplicated that temple file for every employee.

So in the end, if I should have 100 excels files (one per employee) with different name and differents Alteryx tab (all the other tabs should remain the same) . I guess it requires a batch macro but not sure

Is that possible?

Thanks

Re: Output & duplicate template file

jrgo — Wed, 13 Dec 2017 01:06:21 GMT

@Hakimipous,

While, theoretically, this is possible, there's not a simple solution that I'm able to think of.

It may be possible to accomplish without creating a macro. Outputting to separate files is easy. Outputting to a pre-formatted sheet (template) is also fairly easy. However, doing both of these is what complicates because outputting to a template requires that the template already exist, which it does, but only once.

Your workflow will FIRST need to make copies of your template with the appropriate file name BEFORE it outputs. You can potentially do this using a Run command or the Blob input/output tools.

Next, you'll need to create a field that would contain the full file path, including directory, file name.ext, sheet name and output range (e.g. \mydirectory\employeeXYZ.xlsx|||Alteryx$C3:Z100). This field would be used in your Output tool, checking the "Take File/Table Name From Field" option and using "Change Entire File Path", which is where you'd reference the new field created.

Again, completely theoretical, but hope this gives you some ideas...

Best of luck and happy to continue bouncing ideas!

Jimmy

Re: Output & duplicate template file

Hakimipous — Wed, 13 Dec 2017 01:32:04 GMT

Thanks for reading @jrgo

Outputing my XYDB into seperate files was easy. Same for outputing my all of my XYDB into a single sheet.

But generating one template per employee with one sheets only that differs still bugs me.

Do you think this will help ? https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Output-multiple-files-using-the-same-excel-template/ta-p/20493

I've never used the command tool in alteryx

Re: Output & duplicate template file

jrgo — Wed, 13 Dec 2017 02:04:01 GMT

@Hakimipous,

Yeah, no easy approach as I had mentioned. However, that KB article is exactly the approach I was thinking.

Re: Output & duplicate template file

Hakimipous — Wed, 13 Dec 2017 12:20:51 GMT

@jrgo

What if I was able to make copies of that template file with a bat file with the Windows command.

Let's assume I gave up on alteryx to create 100 copies and do it direclty on Windows.

Creating enough copies (one per employee) is not difficult, but once I've done that, the difficulty is how to make alteryx ouptut each employee to each file, to get as result every copies I've created filled with the different data of the employees

Not sure if that simplify a bit the situation?

Re: Output & duplicate template file

jrgo — Wed, 13 Dec 2017 15:01:56 GMT

@Hakimipous,

To answer your question, yes, that would simply, but it wouldn't be a dynamic solution based off the employees contained in your data. Basically, if there's a new employee, you'd have to modify your BAT before your run your Alteryx Workflow.

In my original reply, I also mentioned that you can use the blob tools to create copies of your template. In this scenario, I believe this would be sufficient instead of creating a BAT script.

Attached is a solution I mocked up that would use a template, create copies and renaming it using a specific value derived from the data (employee ID in this example) and then outputting to these newly created copies.

I did develop this workflow in 11.7, but I don't think I'm using any tool/function that's specific to this version so if you don't have this version installed, you can do the xml hack (which you can search how to do throughout community) to the version you have.

Re: Output & duplicate template file

Hakimipous — Thu, 14 Dec 2017 13:45:49 GMT

Yes you are right, it would be only a temporary solution

I've succeed creating a Bat file, however I will still try your solution as it seems a bit more simple.

Again thanks for the help x)

Re: Output & duplicate template file

jrgo — Thu, 14 Dec 2017 13:50:44 GMT

@Hakimipous,

Were you able to get the issue figured out? I saw that you had edited your response, but it looks like you re-edited it again and removed what you had added.

Re: Output & duplicate template file

Hakimipous — Thu, 14 Dec 2017 14:22:51 GMT

@jrgo

Yes sorry about this I had a wrong manipulation in my workflow.

You method works perfectly and has the advantage to work even on a Network location (contrary to the Batch file ) + macro method. I will switch and adapt to your method as it is more flexible.

Thank you so much for this !

Re: Output & duplicate template file

jdavey — Fri, 09 Feb 2018 10:45:49 GMT

@jrgo,

Thanks so much for the solution - I have got this set up and working as desired.

However I am finding that the blob output is creating both an xlsx and an xlsx.bak copy of my template file - any ideas how this can be avoided?

Thanks!

Re: Output & duplicate template file

Hakimipous — Fri, 09 Feb 2018 10:49:50 GMT

Hey @jdavey

There is no way (for the moment) to prevent the BAK files copies from being created.

Re: Output & duplicate template file

Cbennett022 — Wed, 04 Mar 2020 05:21:54 GMT

I think this approach may be able to help me but wanted to ask some additional questions. I have an excel template that is formula driven and pulls data from a 'Master Data Input' tab. There are seven sheets inside this excel that are populated based on this input tab.

The input tab has data grouped by company e.g. ABC, DEF, GHI etc.

The seven sheets pull the data and then calculate based off of each other. For example one sheet is year to date detail, the following is only Q1 the second is Q2, which pulls YTD and backs out Q1 and so on.

Can I use this approach to replicate this workbook and all worksheets within it for each company code in the 'Master Data Input' sheet? It would create roughly 300 files.

Additionally, it would be ideal for each workbook to only contain the relevant company data on the input tab instead of the exhaustive list.

Any and all help would be appreciated. Thank you!

Re: Output & duplicate template file

Cbennett022 — Sat, 14 Mar 2020 00:23:04 GMT

I have an excel template that is formula driven and pulls data from a 'Master Data Input' tab. The input tab has data grouped by company e.g. ARES, CAES, etc.

I am able to replicate my templates but the master data input tab contains data for all companies. Is there a way to have the master data file only include the company data for each respective template? The amount of line items may change depending on data so a static data range will not work.

Re: Output & duplicate template file

data4accounting — Thu, 17 Feb 2022 05:10:22 GMT

I used the bottom half of this workflow to copy template files for each legal entity and it is incredible! Thanks! Alteryx seems to teach me something new every day that I can do.

Re: Output & duplicate template file

rrahu07 — Sun, 22 May 2022 13:09:20 GMT

@jrgo / @Hakimipous can you please help me with one of my questions? I posted it twice and still no luck.

HELP!! - Alteryx Community

Re: Output & duplicate template file

SH2023 — Thu, 07 Sep 2023 18:43:13 GMT

Hello Hakimipous,

I saw your Alteryx workflow design file on the chatting. Do you have the 2 input data file on this workflow to be shared so that I can see the Output on the design.

Appreciate your help.