cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
asteryx
Asteroid

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

ZHENGCH
Meteor

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

ZHENGCH
Meteor

Sorry meant to say Adam instead of Simon

Alteryx
Alteryx

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?

Adam Riley
Principal Software Engineer
Tech Lead Core Engines
Alteryx
ZHENGCH
Meteor

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

Alteryx
Alteryx

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

 

Excel.png

 

 

Adam Riley
Principal Software Engineer
Tech Lead Core Engines
Alteryx
Bolide
Bolide

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

asteryx
Asteroid

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

Fiona
Atom

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

Highlighted
Quasar
Quasar
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!