Input Multiple Sheets from the same Excel

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.

Input

Accepted answers

All comments

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/td-p/344867

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703