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

Alteryx designer Discussions

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

Is there a way to union 40 + excel sheets into one excel sheet at the same time?

Asteroid

I tried the union tool and after 5 sheets it didn't see the others.

Each sheet is set up identically.

Any suggestions would be greatly appreciated.

 

Alteryx Partner

How are you reading in the sheets? Are they all from a single file with 40+ different sheets, or spread over multiple files?

 

If you could add a snippet of your workflow it would be easier to see exactly what's going wrong

 

Cheers!

Asteroid

I'm trying to union 40+ sheets from 40+ excel workbooks. 

The solution I've come up with so far has been to append one sheet at a time into a master excel sheet.  

Alteryx Partner

In that case, since the schema is identical between sheets, the Directory Tool -> Dynamic Input tool is exactly what you're looking for. If you could provide a screenshot, or a description of the configuration you have set up, and I can likely help you debug the issue.

Asteroid

I've attached a sample of what my basic workflow looks like.

The only difference is I have 40 + data input tools in one work flow.

I found an article about the Dynamic input tool but didn't know where to look for it or how to set it up.

Thanks for the help with this project.

 

Alteryx Partner

Here's a screenshot of what your setup should look like.

 

I've created 5 files with the same headers as you, and just 1's as the only row as sample data (but this shouldn't matter, the important part is that the column names are the same in all files).

 

Here's a screenshot:

dynamicinput_0906.PNG

 

 

There's two parts to this workflow:

 

1) Directory: You'll want to point this towards the directory your excel files are in, and if there are any other files in that folder, change the "File Specification" configuration to match the ones you want to be read. I've set it to "*.xlsx" which will read all excel workbooks. The "*" acts as a wildcard meaning "anything"

 

2) Dynamic Input: First, select one of your workbooks as the Input Data Source Template. It is important that all other sheets have the same column names as this template. Then, you'll select "Read a List of Data Sources", choose the "FullPath" field, and change the "Action" configuration to "Change Entire File Path".

 

This should allow you to read in all files in that folder, as desired.

 

Let me know if this workflow doesn't work for some reason, or there's some complication I've missed.

 

Cheers!

Labels