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

Alteryx Knowledge Base

Definitive answers from Designer experts.
New Data Science Blog

Check out the latest post: All Models Are Wrong

READ MORE

Input Multiple Excel sheets with a Dynamic Input tool

Alteryx_KB
Import

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).

excelinput.png

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.

excelworkflow.png

 

 

 

exceltextinput.png

 


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".

 

exceladdquery.png

 

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: 

 

replacestring.png

 

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


DynamicInputExcel.png


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.



readlistexcel.png

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.


exceloutput.png

Comments
Atom

Hello,

 

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.

 

Capture.JPG

 

Capture1.JPG

 

 

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

 

Thank you.

 

Best regards,

Kateryna

 

 

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,

Adam

Meteoroid

Very helpful. Thanks!

Asteroid

Hi

 

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.

 

 

Meteor

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?  

 

Meteor

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

Moderator
Moderator

Hello @JMoore,

 

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

 

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Error-s-when-opening-XLS-files-after-applyin...

 

Thanks!

Alteryx Partner

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

Moderator
Moderator

Hi @bb213,

 

You may want to take a look at this article:  

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

 

 

Meteor

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

Meteoroid

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

Asteroid

 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 !! 

Moderator
Moderator

Hi @Fz,

 

Please reference the below article for your error 

 

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Error-s-when-opening-XLS-files-after-applyin...

 

Thanks,

Meteoroid

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

Meteoroid

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

 

2018-04-13_10-13-48.png