Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More

Input Multiple Excel sheets with a Dynamic Input tool

Created on

Suppose that your spreadsheet has multiple sheets with the same structure and you would like to read several sheets into your module at once.  Your first reaction might be to use one Input tool per sheet, followed by a Union tool to bring all of the data together.  In this case, the preferred alternative is to use the Dynamic Input tool.

In this example, we have demographic data for 5 states, where data for one state is in each of five sheets inside of an Excel file.  In this scenario, we would like to input the data from only 3 of the five sheets (Alaska, Arizona, and California).


We can use the Text Input tool to specify the names of the three sheets, see the following image.  We then connect to a Dynamic Input tool, which we configure to use the Sheet names that we have specified in the Text Input tool, as the means of updating the “File/Table Name” that the Dynamic Input tool will be importing.







Dynamic Input Tool configuration

From the Input tool configuration, browse to the Excel file and select one of the sheets or tabs, this selection will serve as a placeholder.  Click on the “Modify SQL Query” radio button for the Table or Query, and then on the right side of the configuration window click the drop-down button that says "Add" and select the option that says "Replace a Specific String".




The modular window that pops up should have the statement: SELECT * FROM `Alabama$`  under Text to Replace: and the field from your Text Input tool should be populated under Replacement Field: 




When the module runs, the ‘Alabama$’ text will be replaced with the table names that were specified in the Text Input tool.


Now, select the radio button for “Read a List of Files”; for the “Field” selection dropdown, select the field name that was created in the Text Input tool (in which the sheet names were typed), and set the “Action” to Change File/Table Name.


After running the module, you will notice that records from the three different sheets will be populated in the table view of your Browse.  A great option to take advantage of in this process is Option #5 “Output File Name as Field”, which allows you to append the full file path or file name as a field for every single record.  This is a great means of allowing you to quickly group-by and organize your data according to the sheet from which it was sourced.





could you please help me, it seems to me I do the same, but have error.


I have 4 same Excel files in a folder. I have connected Directory tool to the folder and in Dynamic input I am trying to open the Excel files which are in the folder.

I get an error: Unable to open file for read.







Could you maybe describe all the steps I have to perform?


Thank you.


Best regards,




I have a similar situation with a few different factors involved.  I have an excel file with 20 tabs, and I want to combine 15 of the tabs together.  The 15 tabs have the same headers, but the headers are in row 5.  One of the issues I'm having is that the info above the headers is what alteryx pulls to determine column headers, and that data is different per tab, which I don't care about.  


1. I basically want to copy and paste all the rows (except 1-4) from each tab into one worksheet. The order of the tabs does not matter.

2. Cell A3 of each of the 15 tabs is a code.  I want to insert a column in the very beginning of the worksheet (would be column A) and copy the code from A3 into each row correlating from that tab it came from. Perhaps this step should come first.


What are the proper steps in this case?


Kind regards,



Very helpful. Thanks!




I am also facing the issue as below. I am pretty sure that I am having same settings as given in attached sample


 Dynamic Input (2) Error opening table: Microsoft JET Database Engine: The Microsoft Jet database engine could not find the object 'Apparel'.  Make sure the object exists and that you spell its name and the path name correctly.\3011 = -543884569.




Thank you, very helpful. I have an issue wit one of my sheets, it's an Excel file, and brings all the empty cells in the final file (where all the sheets are aggregated). Do you know what could be the reason?  



Same issue here as well, getting Microsoft JET Database Engine: Unexpected error from external database drive (1).\3275 = -535434136


Hello @JMoore,


I'd recommend checking out the following community article, where we detail this particular issue and some workarounds.



Alteryx Partner

This only works if all the sheets have the exact same headers.  This is very rare when every sheet is formatted identically. 


Hi @bb213,


You may want to take a look at this article:  

Read in Multiple Excel Files, with Multiple Tabs that have Different Schemas




using a macro worked perfectly for pulling in all the different sheets


I had a similar situation. This worked great. Thanks!


 Hello @JMoore


same issue : getting Microsoft JET Database Engine: Unexpected error from external database drive (1).\3275 = -535434136

How did you solve a probleme 


thank you !! 


Hi @Fz,


Please reference the below article for your error




This may be a silly question, but where do you get the option to select the SQL Editor?


Hi @alondergan


I have numbered the clicks below from one of my workflows. I am assuming you mean the SQL editor in the dynamic input tool




What if your excel sheet don't have similar structure/fields, what are your options on inputting multiple sheets at once?

Alteryx Partner
Alteryx Partner

Thanks. I have only used the radio button 'Read a List of Data Sources' and it's working fine.


PFB my configurations:



And I get the output of all the 3 sheets (2017,2018,2019) in a single sheet !


Thanks for the help with Dynamic Input Tool.


What if each of the Sheets that I want to import has a blank first line?  All of the headers are in Line 2 and the data begins on Line 3 of each Sheet.  The workflow runs with no errors but the table in my Browse tool shows all NULL values and all of the columns I woudl like to see do not appear.


I can get the file name, but not the sheet name to add as the additional column. Can someone explain how to change if from file name to sheet name?


Alteryx Partner



In the dynamic select tool, you need to click "Edit" on the "Input Data Source Template" and then change option 5 to "Full Path".  You then need to separate the Tab from the rest of the file name using "Regex" and the "Tokenize" option. The expression is ([^|||]+$). Let me know if you have further questions.