Alteryx designer Discussions

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

Bring in a Sheet From Multiple Workbooks

Highlighted
11 - Bolide

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 

Highlighted
Alteryx
Alteryx

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.  

 

Wildcard.jpg

 

 

 

 

Highlighted
11 - Bolide

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

Highlighted
12 - Quasar
12 - Quasar

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. 

 

 

directory1.JPG

 

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

 

 

 

Highlighted
11 - Bolide

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

Highlighted
11 - Bolide

@Treyson No matter what I do, I get the below error. 2017-07-06_11-20-24.jpg

 

 

Highlighted
12 - Quasar
12 - Quasar

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

Highlighted
11 - Bolide

 

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

 

2017-07-06_11-30-12.jpg

Highlighted
12 - Quasar
12 - Quasar

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

Highlighted
11 - Bolide

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.

2017-07-06_12-42-21.jpg

Labels