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

Output & duplicate template file

Hakimipous
10 - Fireball

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

15 REPLIES 15
jrgo
14 - Magnetar

@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

 

 

Hakimipous
10 - Fireball

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

 

I've never used the command tool in alteryx

 

jrgo
14 - Magnetar

@Hakimipous,

 

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

Hakimipous
10 - Fireball

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

jrgo
14 - Magnetar

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

image.png 

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.

Hakimipous
10 - Fireball

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) 

 

 

 

jrgo
14 - Magnetar

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

 

 

Hakimipous
10 - Fireball

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

 

 

jdavey
5 - Atom

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

Labels