Trying Batch Macro for the First Time

[katherinetdavita]

Hello-

We recently started having issues with a workflow we've been running for several years - we're getting the ___ File have a different schema than the first file in the set. Seems like the files we input suddenly have a schema that doesn't work with our existing workflow. From my research, I think a batch macro will fix it, and I think I'm on the right track, but I'm not sure I've got it right because I'm still getting an error.

I followed the directions in step three of this post, but I've seen another post suggest I need a batch macro tool instead. 

Our existing workflow used a directory tool to go out to a location where daily files are saved, then a sort to find the most recent by creation date, then sample tool to reference only the 10 most recent files. From there we've used a dynamic input tool to specify a template, and that where I get the error. Here's what my first attempt at incorporating a batch macro looks like. I'm not sure I have the Control Parameter and Update value tools in the right place, and I'm not sure how the output tool is supposed to work, though now that I'm thinking of it, I suppose it need to go at the end of my workflow, but I still need to add back in the rest of the many tools from the rest of my existing workflow. Basically i just need to know what the beginning looks like for bringing in all these files that are now of varying schema. Help! I'm lost and I need to fix this before I need to run this critical report again next week. :)

[messi007]

@katherinetdavita,

That's mean that you have different schema on the files.

You can add the interface designer :

Then check 2 seconde option : 

Hope this helps !

Regards,

[katherinetdavita]

Thanks- I had done that but when I copied these pieces into a new workflow it went away. Can you tell me more about the custom item and how that works/what I would be selecting there? I had initially chosen Standard icon based on something else I read on another post.

[messi007]

@katherinetdavita,

For the custom item they are just icone you can use them or not.

So if you select stantard icon it will be fine for you.

For this part :

• auto-configure by Name (you’ll notice this looks a lot like the Union tool configuration window); this unions all the files together, regardless of difference in schema - any columns not present in some of the files will be filled with NULLS.

Hope this helps,

Regards,

[MarqueeCrew · Accepted solution]

@katherinetdavita ,

Based on your "verbal" simple requirements, on ZOOM I helped you to create a SUPER DYNAMIC EXCEL INPUT tool that reads SHEET1 data from any .xlsx files regardless of format (using tip from @messi007 .  In your needs, you formatted the input file to read starting on row 10 and to output the FILENAME.

This is a simplified BATCH macro that requires 0 configuration.  Feeding this batch macro data you have a DIRECTORY tool that eliminates all non-FULLPATH fields after you select (via sort and sample) the 10 most recent files.  The only field, FULL PATH is then connected to the BATCH macro and magic.  It works.

Cheers,

Mark