Output data into excel template - header issue

[zanjam]

Hello

I'm having an issue with running my batch macro to populate an excel template per person because one of the headers is the individual's name therefore the schema changes between each iteration (causing the batch macro to fail). Any ideas on how I can keep the keep a dynamic header for an individuals name or how to output to an excel template without including the header?

Cheers

[BenMoss]

Please share images of exactly what you are trying to achieve and some example data if possible.

Ben

[zanjam]

Hey Ben

I've attached the batch macro which iterates through files in a directory to map a data range to a specified data range in an excel template. The issue is Alteryx maps the header column as the first row of data so I used the Dynamic Rename tool to solve for this. However my new issue is that the header is now the individual's name which will change through each iteration. This causes an error as the schema changes.

Any ideas on how to solve for this?

[ddiesel]

Hi @zanjam!

I couldn't run the macro because the dependencies weren't uploaded, but you can try to change the macro output settings in the Interface Designer.

I'm not sure the format of the data, but you probably need "Auto Configure by Name" or "Auto Configure by Position"

If you choose "Auto Configure by Position", you need to control the number of fields and order Select Tool (52). Uncheck "John Smith" and make sure *Unknown remains checked.

If that doesn't work, please upload some dummy data, and I'm sure @BenMoss will be able to let us know the solution.

EDIT: I just noticed that you did upload a .yxzp. I changed the macro output settings and it worked for me. Let us know if it works for you!

[zanjam]

Hey @ddiesel

Thanks so much! I updated macro output settings to "Auto Configure by Position" but by unselecting the "John Smith" column and keeping the unknown field ticked, I get a "No table is chosen, please table from data source" error. 

I've attached the two dummy data files that I've stored in a directory for the macro to read through and update the excel template. Maybe I'm missing something super obvious?

[ddiesel]

Hi @zanjam

Can you also upload a sample of "Dummy Template 1.xls|||DummyRange" and also a sample of what you want the final output to look like?

[zanjam]

Here you go @ddiesel

Let me know if you need anything else, appreciate your help!

[ddiesel · Accepted solution]

Hi again, @zanjam!

I think you only need two small modifications to get this working.

First, the Output Tool should be inside the macro so each iteration ends by overwriting a template, instead of trying to bring the data all together again in the workflow.

Workflow Before:

Workflow After:

Second, you need to control the portion of your macro that outputs the imported blob template, so that the template is created before the output file is written. You can accomplish this with the Parallel Block Until Done (Crew Macro).

Macro Before:

Macro After:

I replaced all the dependencies and ran on my machine with these two modifications. Everything seemed to match your SampleOutput with new files created: 12345.xls for John and 36272.xls for Bob.

Please give this a try, and let us know if it works for you!

[zanjam]

This is amazing!! I didn't know about the crew macros until now. I made those small tweaks and its working great

Thanks again @ddiesel

[ddiesel]

You're welcome!

And thank you! I really liked your "blob" concept to create the templates, and I might be able to use something similar.