Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

Input Multiple Excel sheets with a Dynamic Input tool

Alteryx_KB
Alteryx
Alteryx
Created

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
Kateryna
5 - 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

 

 

AdamMCohen
5 - Atom

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

HeliaS
6 - Meteoroid

Very helpful. Thanks!

anbugans
8 - 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.

 

 

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

 

JMoore
8 - Asteroid

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

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

bb213
8 - Asteroid

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

JessicaS
Alteryx Alumni (Retired)

Hi @bb213,

 

You may want to take a look at this article:  

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

 

 

JMoore
8 - Asteroid

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

bobro
6 - Meteoroid

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

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

JessicaS
Alteryx Alumni (Retired)

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,

aimeegoins
6 - Meteoroid

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

bobro
6 - 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

IJH34
8 - Asteroid

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

maryas
8 - Asteroid

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 !

jaycaplan
6 - 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.

Jhar2401
6 - Meteoroid

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

bb213
8 - Asteroid

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

Rajeev18
8 - Asteroid

Hello ,

How to get multiple tab with different color based on some condition in one excel under different tabs. 

Example: -

OUTPUT file:- Different Tab in one worksheet with different name for each Tab and based on color condition

Rajeev18_0-1576766746222.png

 


1 Input file: New York

Product IDProduct Name1st level Approval statusDate2nd level Approval statusDate
12345MIn Transmit12/13/19Submitted12/13/19
23456NIn Transmit10/19/19Submitted10/19/19
98766ZAccepted12/13/19Not sent12/13/19
45678AAccepted11/25/19Accepted11/25/19
32156BRejected12/13/19Rejected12/13/19
23457CWIP10/29/19In Transmit10/29/19
45678WSubmitted12/11/19In Transmit12/11/19
12980XAccepted12/11/19Accepted12/11/19
45987YRejected12/01/19Submitted12/01/19
345678ZMay get Rejected12/11/19Accepted12/11/19
234568QIn Issue10/27/19Submitted10/27/19
98765sAccepted12/13/19In Transmit12/13/19

 

2. Input file: California

Product IDProduct Name1st level Approval statusDate2nd level Approval statusDate
12345MIn Transmit12/13/19Submitted12/13/19
23456NIn Transmit10/19/19Submitted10/19/19
98766ZAccepted12/13/19In Issue12/13/19
45678AAccepted11/25/19Accepted11/25/19
32156BRejected12/13/19Rejected12/13/19
23457CRejected10/29/19May get Rejected10/29/19
45678WSubmitted12/11/19In Transmit12/11/19
12980XAccepted12/11/19Accepted12/11/19
45987YRejected12/01/19Submitted12/01/19
345678ZMay get Rejected12/11/19Accepted12/11/19
234568QIn Issue10/27/19Submitted10/27/19
98765sAccepted12/13/19In Transmit12/13/19

 

3.Input file:- San Francisco

Product IDProduct Name1st level Approval statusDate2nd level Approval statusDate
12345MIn Transmit12/13/19Submitted12/13/19
23456NIn Transmit10/19/19Submitted10/19/19
98766ZAccepted12/13/19In Issue12/13/19
45678AAccepted11/25/19Accepted11/25/19
32156BRejected12/13/19Rejected12/13/19
23457CRejected10/29/19May get Rejected10/29/19
45678WSubmitted12/11/19In Transmit12/11/19
12980XAccepted12/11/19Accepted12/11/19
45987YRejected12/01/19Submitted12/01/19
345678ZMay get Rejected12/11/19Accepted12/11/19
234568QIn Issue10/27/19Submitted10/27/19
98765sAccepted12/13/19In Transmit12/13/19

 

 

RAJAYKUMAR001
7 - Meteor

Hi Team,

 

I am trying to use the similar function.

I have around 70 plus tabs, My tabs are all 2 letters example AA,AB,AC,AD,AE,AF etc...

 

However for some reason Alteryx is not able to read few specific tabs. when I change the tab names it works but it does not work with the Original Tab name. The following is the error.

 

Dynamic Input (2) Invalid range: "BB". Check sheet to make sure the range exists, or specify a new range.

 

Could you please let me know If I have to do something. Since it works when the name is changed and not with the original tab name.

 

RAJAYKUMAR001
7 - Meteor

Is there a way that we can also have Tab name in the output on a new column ?

 

so that we can go directly to that tab if we need any modifications

 

jroddy
5 - Atom

Worked perfectly, thanks!

roshan_dsouza23
6 - Meteoroid

Working with .xls however I first need to do a Dynamic path derivation followed by looking up multiple files in a directory and then under the multiple files multiple tabs.

 

I have done the first 2 bits i.e. Dynamic path derivation and Lookup all files , however seems like Dynamic Input doesn't have the flexibility of recursively reading through multiple tabs in each of the files.

Any thoughts as I cant apply Report* or Report$ in the Formula tool before providing it to the Dynamic Input. It treats it as a char and not a regex

 

MultiTab lookup.PNG