using dynamic input on excel files with different tab / worksheet names

[simon]

Here's my dilemma. I have 200 excel spreadsheets but they all have different/unique worksheet names (so not all Sheet1). I would like to create one output from all 200 sheets.

I used the directory tool to get fullpath which feeds into the dynmaic input tool. The DI tool works great but ONLY when worksheet name or tab is the same in all files (sheet1). Same is true when just using input tool and doing *.xlsx.

How can I get input / dynamic input tool to work when all excel files contain different worksheet names (LA, NY, ATL, etc). Has anyone found a workaround for this? (I am not about to drag 200 sheets to my canvas and merge them since this will become an app.)

Thanks,

Simon

[asteryx]

Thanks very much for this, @Joe_Mako.

I worked for ages on two other approaches - a batch macro that had a dynamic input to change the sheet names, and the double dynamic input suggested by @AdamR_AYX. But neither worked when I was trying to specify a range within the sheets. (I have multiple sheets with different names in multiple files - though all the sheets have the same structure.)

Yours worked like a charm!

I added a few things to the workflow to clean up the results:

- a filter to remove rows that were empty (one of the columns is null)

- a select to exclude columns that the macro captured but weren't actually part of the Excel form

- an Imputation tool to convert null number fields to zero

I also noticed that the sheet names exclusion option errored if the sheet name was longer than 6 characters. I think that is fixed if I change the field size in the select tool in that part of the workflow in the "outer" macro.

Thanks again.

[cbz]

Hi Simon

I just come cross with similar issue and found your solution.

It is quite close to what i am looking for, However I still face an issue that my input data source are actually from 2 different folders.

Is there anyway to solve this?

Please find the attachment.

Thanks

Chunbin

[cbz]

Sorry meant to say Adam instead of Simon

[AdamR_AYX]

Have you tried using @Joe_Mako's macro?

Are you able to share a zip of some sample excel files in a directory structure similar to yours?

[cbz]

Hi Adam

Yes, that one works fine if all the worksheet is saved in same folder address?

Where as I am now have 2 address. EG: L:\My Documents\Desktop\Alteryx Research\201701\Worksheet1.xlsx

                                                                 L:\My Documents\Desktop\Alteryx Research\201702\Worksheet2.xlsx

The red color dates are different folders.

I have attached sample also in the alteryx workflow I have written a new one what I am thinking to be. reduce 2 processing row into one.

Please find the attachment.

Thank you for your time.

Thanks

[AdamR_AYX]

It will work fine across multiple folders too.  Just be sure to check the "Include SubDirectories" option and point it at the parent folder like so

[pcatterson]

The Dynamic Input can pull data from various folders.  Change the Field to a 'FullPath' and the 'Action to Change Entire File Path'

[asteryx]

@Joe_Mako's macro includes an action tool that automatically updates the Include Subdirectories checkbox in the directory tool inside the macro. So you can use the parent directory with the macro. You can also add a filter to force the macro to only pull files where the filename is like a known pattern - in case you've got other files in those directories that you want the workflow to ignore.

[cqwang]

@Joe_Mako

The macro tool is really helpful, thank you~!

but I encountered an issue that I have two sub-folders within a parent folder and in Directory I specifically only bring in the parent path. However, the macro only output one of the subfolder. When I delete the subfolder in parent folder and rerun the macro, it then recognize and output second one. Not sure why this happened, any ideas?

It's like the path for Parent is : C:\Users\Templates

and two sub-folders are C:\Users\Templates\A                 C:\Users\Templates\B

However, when I run the macro, it only output full path start with C:\Users\Templates\A

If I delete folder A from Templates folder and re-run exactly same macro, it then able to output C:\Users\Templates\B

I think it might be I have too many fields in excel file, cause one of the run the macro only picked up 2 of 3 files included in the same folder. If so, anyway to fix it?

Thank you~!

[Joe_Mako]

I am sorry it is not working. Please send me an email joemako@gmail.com and we can setup a time to meet for a Webex to resolve the issue. Thank you for reaching out!