community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More

Process Multiple Excel Files with Multiple Worksheets - All files have same format

Highlighted

Hi, 

 

New to the boards and needing a little help. I have about 200 Excel files with different names that i'm needing to process. They each contain about 10 worksheets that need to be read in. I'm struggling to find the best way to do this. 

 

Also needing to capture the unique Excel file name as i'm reading each of the 10 worksheets and the worksheet name as well. Any chance anyone in the community has dealt with this? 

 

Thanks in advance.

J

Alteryx
Alteryx

Hi @JonathanJ 

 

You should be able to use my macro here: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Input-Batch-Macro-AKA-the-Extra-Dynami...

 

This returns the file and sheet name as a column too, so you have that on each row of data.

Thanks for the suggestion Joe. Here's the catch. these excels are formatted horribly. While they are consistent, i'll have to specify specific rows and omit certain columns. I managed to find the select records tool which seems to do the job. I'm wondering if there is a way to reuse the logic you have in your Macro to do the other tasks (capture file name and worksheet name, looping etc.).

 

I haven't worked with Macros in Alteryx but would be great to take this opportunity to learn it. Any chance you have some thoughts on this?

 

Thanks Joe,

Kevin

Alteryx
Alteryx

Ah interesting challenge.

 

Is it always the same specific rows and columns to emit?

 

If so, you can open up my macro (right click > Open macro) and then select the inner macro near the top that deals with Excel files to do the same.

 

Then add the required logic in there.

 

That way each batch, or to think of it differently, each file will have that same logic applied to it.

 

If it's more dynamic than that, it will need a slightly more complex solution.

Labels