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

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
#SANTALYTICS

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

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.

Input Multiple Excel sheets with a Dynamic Input tool

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

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

Attachments
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?  

 

Asteroid

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

Alteryx
Alteryx

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

 

 

Asteroid

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

Asteroid

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:

 

Table_Multiple_sheets.PNGMultiple_sheets.PNG 

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

Meteoroid

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.

Atom

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?

Thanks

Alteryx Partner

@Jhar2401

 

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.