Bring in a Sheet From Multiple Workbooks

[smoskowitz]

Hi All --

Ok, next question. I have 3 Workbooks named:

1) Cash Taxes for Treasury Q4 2014.xlsx

2) Cash Taxes for Treasury Q4 2015.xlsx

3) Cash Taxes for Treasury Q4 2016.xlsx

I need to bring in the same sheet from each 'Cash Taxes - WW$'. Individually, each of these sheets works with my workflow -- but how to I bring in all three at one time to get a single dataset?

Thanks, 

Seth 

[NickC]

Hello Seth,

Try reading in the file with a Wildcard (*).  

So in my example I have replaced the Year within your file name to a *.  This will automatically open all of the files and stack them on top of each other.  This will only work providing the sheet name is the same across those files.  

[smoskowitz]

I tried that and it only brings in one year. I have attached my workflow and 2 years for files. Any help you could provide would be appreciated.

Seth

[Treyson]

If everything is structured the same way, you could use the directory tool with a dynamic input. If you have those files in the same folder, you can set up the directory tool to look for all files that meet a specified criteria in that folder. 

Now that you have the full path for all the files that meet your criteria, we can feed that into a Dynamic Input tool to go out and pull all of the sheets with those specific names in those specific files (make sense). You need to initially set up the dynamic input tool the same as if you were setting up a regular input tool to go look for a file, and then configure like that above.

If your tables aren't structured the same there is a chance that this method will break, but give it a shot and lets see what comes back at us.

On the chance that they are structured differently @Joe_Mako has a bunch of stuff out there where he will create macros to solve similar issues.

Treyson

[smoskowitz]

Thank you for your input as its very helpful. For the most part, the sheets are structured the same -- data-wise, but there are nuances that seem to make Alteryx cranky. Nothing more than some comments. Let me work through your solution and see if it leads me in the right direction.

Seth

[smoskowitz]

@Treyson No matter what I do, I get the below error. 

[Treyson]

Can I see your current settings and the full error message?

[smoskowitz]

Error: Dynamic Input (2): C__Cash Taxes_Cash Taxes for Treasury Q4 2014.xlsx does not match a sheet or named range in C:\Cash Taxes\Cash Taxes for Treasury Q4 2014.xlsx or the Excel file is corrupt.

The file is not corrupt in any way. I have taken the source several times and several years of this file.

Thanks,

Seth

[Treyson]

On the action Drop Down in the configuration panel, change the action to "change entire file path".

[smoskowitz]

Ok, its closer, but I am now getting a new error message:

I have a sheet selected, as you can see from the highlighted text, Not sure why it doesn't recognize it. Thank you again for your help.