Excel Input Data - Do I need a Macro?

I am working on a project, users will update comment fields in an Excel file that lives on a shared folder.

My Alteryx Workflow will be scheduled to run at the end of the day to capture all the updated info from user comments in all 29 files, and push that into a table in our Snowflake database.

The issue is this:

1. There will be 29 separate files in the shared folder, one for each of our business areas. The names will be in a format like...

"Daily Report SOUTHEAST 20181120.xlsx"

2. The tabs in the Excel spreadsheet will be named similarly to the files - they'll have the date dynamically, and the area in it - so the tab I need to capture in the file in #1 above might be called OUTS SOUTHEAST 20181120... the tab will always be the 2nd tab, but they will be named uniquely - 29 different names

3. Because of the file and tab naming, I don't believe I can just use a simple wildcard with an Input Data tool. My experiments with that have failed so far.

I believe I need to create a Macro tied to a Dynamic Input tool for this to function properly, but I haven't done something like that yet, so I am lacking confidence in that area

Has anyone seen a situation like this? Am I on the right track?

Thanks

Input

Accepted answers

Claje

Hmm.

It seems that the Dynamic Input tool (and the input tool itself) only allocate enough space for the longest sheet name each time they read in a file. Because of this, the process cannot or does not read in additional excel files if the length of sheet names changes.

To get around this, I've attached a batch macro that I built in September which should resolve this issue.

If you replace the first Dynamic Input tool in this example with this macro, it should pull in the sheet names correctly.

I had to attach it as a YXZP file because of errors with the Community, but Alteryx can extract that without issue.

The macro should be relatively straightforward to configure, but if you run into issues, let me know!

Claje_LoadMultipleExcelFiles.yxzp

All comments

Claje

How I'd look at doing this:

I'd use a Directory Tool to get a list of all the files in the shared directory that meet your criteria.
Then, I'd use a Dynamic Input tool configured to pull in a list of Sheet Names for each file (and to output the file name).

From there, you can use a filter, either by the sheet names per your naming convention rules, or to pull in specific rows from the list of sheet names.

Then, a second Dynamic Input tool can be used to pull in your data.

I don't have time right this minute, but I can put together a proof of concept a little later today if the above description does not help.

Let me know if you have any questions!

michael_heitz

I think that is how I started... trying to use the Directory tool... then got bogged down and questioning my role in the universe. LOL

I'll keep working down that path, but any advice along the way would be fantastic

DavidxL

Have you already seen https://community.alteryx.com/t5/Engine-Works-Blog/The-Ultimate-Alteryx-Holiday-gift-of-2015-Read-ALL-Excel-Macro/ba-p/9988?

That'll help read in the data and list out all of the sheets of all of the Excel files in the folder. Then you can use the Tile tool or Multi-row tool to figure out the sheet name of the 2nd sheet in each workbook and then start your extraction / processing from there ...

michael_heitz

thanks - will review this and work through over the long weekend

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703