Input Multiple Sheets from the same Excel

[Jmacnevin]

Hello,

I have a single Excel file that contains multiple sheets.  Each sheet has a different format.  I need to prepare and analyze each sheet in a single workflow on a monthly basis.  Currently, I'm using multiple Input Data tools, and selecting the required sheet.  Is there a way for me to use a single Input Data tool, and pull the sheet i need from that tool to complete the analysis flow I need for that sheet?  Thanks.

[Hsandness]

This article by @mceleavey will be a good starting point.

Article Link: Solved: Reading Multiple excel Files (Only select few shee... - Alteryx Community

[Jmacnevin]

Thanks for sharing.  Interesting solution, however it's not quite solving my problem in this instance, I think because each sheet doesn't have the same format.  

[alex]

Are the sheets the same data with different format like extra columns, missing columns or different order?  Or is the format different because they are separate data tables like project, hours, employees, rates?

[Jmacnevin]

Completely separate data tables.  The first sheet in the workbook is a reconciliation, and the other sheets are separate data tables (each in different formats) that the reconciliation draws from.  I need to do an analysis of the reconciliation, and a couple (but not all) of the additional sheets.  I was hoping to avoid have 4 Data Inputs in my workflow that would have to be updated every month.  I guess an alternative would be to maintain the 4 Data Inputs, but structure them in a way that the connection dynamically updates each month to the new source file.  

[dreldrel]

If each sheet has a different structure, then you cannot process them all at once through a single input data tool, because Alteryx expects consistent column structures when dynamically reading multiple sheets. You can use a Directory + Dynamic Input Tool if the sheets are similar. Hope this is helpful

[Jmacnevin]

Thanks!

[caltang]

I recommend a batch macro approach like this https://knowledge.alteryx.com/index/s/article/The-Ultimate-Input-Data-Flowchart-1583459854309

But tweak it so it reads each List of Sheet Names like this https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Get-worksheet-names-from-excel...

Then add a filter tool before each macro to get the sheet you want OR read everything then dynamically filter with some tweaks according to your needs.