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.

How To: Import Multiple Excel Sheets or a Specific Excel Range into a Workflow

MichaelAd
Alteryx
Alteryx
Created

In this article, we demonstrate how to import multiple worksheets from one Excel file, how to import multiple worksheets from multiple Excel files, and how to import a specific range of cells from an Excel sheet.

How To: Import multiple worksheets from one Excelfile

In the case where you have one Excel workbook with several sheets, you can use aDynamic Input toolto import the sheets instead of an individual Input tool for each sheet.

Note: for this method to work, the sheets of the Excel fileneed to have the same schema.

2019-04-12_13-18-31.jpg

  1. Use anInput Tool to select Sheet Names.


    2019-04-12_13-24-35.jpg


  2. Connect a Dynamic Input tool to the Input tool.


    2019-04-04_12-21-49.jpg


  3. Configure the Dynamic Input tool to read a list of Data Sources from the Sheet Names field provided by the Input tool.

    2019-04-12_13-30-52.jpg


  4. The workflow is now configured to import the Excel sheets in a single input stream (you can see the Regions South and West from the two different Excel sheets).

    2019-04-12_13-34-27.jpg

How To: Import multiple worksheets from multiple Excelfiles

When you need to import multiple sheets from different excel files, you can modify the above method to work by turning it into aBatch Macro.

  1. Start with the same workflow from the previous example.

  2. Add a Control Parameter tooland a Macro Output tool to the canvas.

  3. Connect the Control Parameter tool to the top of the Dynamic Output and Input tools. You should see two Action tools being automatically added to the canvas between the interface tools and the standard tools.

  4. Connect the Macro Output tool to the output stream of the Dynamic Input tool.

    Your canvas should now look like this:

    2019-04-12_13-38-27.jpg



  5. Now, we need to configure the Action Tools.

    The action type should be set to "Update Value". We need only to update the file name without changing the sheet name. Therefore, for both Action Tools "Replace a specific string" should be enabled. Please note that this string should contain the path to the file to input without the extension for the sheet.

    2019-04-12_13-40-27.jpg



  6. If the Excel files have different schemas, in the interface designer we can set the macro to Auto Configure by Name or Position so that our workflow does not error out.Note: sheets within the same file will have to be the same schema.
    1. By default, the Interface Designer window will be displayed in the left-hand side of the Designer window.

      2019-04-22_10-03-25.jpg


    2. Click on the Cog icon in the left bar to access the Properties tab.

    3. Select the Output fields change based on macro's configuration or data input option.

      2019-04-22_8-34-47.png


  7. You now created a Macro. In order to use it, you will need to add it to a workflow.

    1. Save the Macro as a Designer Macro (*.yxmc). Do not close the window with the Macro yet.

    2. Right-click the new workflow canvas and select Insert > Macro.

      2019-04-22_10-20-08.jpg

    3. Any Macro that is currently open in Designer can be added this way to the workflow. Note that it is also possible to create a Macro repository. This will enable to save Macros in one designated place and easily use them in workflows. See this Help Doc article.

  8. Finally, add a Directory tool, and connect it to the Macro's input.

    2019-04-12_13-43-13.jpg


  9. With this configuration, you can import the Excel sheets of multiple XLSX files in the given input directory (you can see Regions North and South from the two Excel files).

    2019-04-12_13-46-00.jpg

Common Issues

Spoiler (Highlight to read)
The file is being used by another process

2019-04-04_12-51-37.jpg

Unable to open file for read: FILEPATH. The process cannot access the file because it is being used by another process.

To resolve, close any other applications (typically Microsoft Excel) that are accessing the file

Some of the columns are not imported, but in Excel the file looks fine

Alteryx uses the drivers that come with Microsoft Excel to import the files. Sometimes 3rd party software does not write data in the correct Excel format.

To resolve, re-save the file in Microsoft Excel. It should now be correctly imported in Designer.
The file is being used by another processUnable to open file for read: FILEPATH. The process cannot access the file because it is being used by another process.To resolve, close any other applications (typically Microsoft Excel) that are accessing the fileSome of the columns are not imported, but in Excel the file looks fineAlteryx uses the drivers that come with Microsoft Excel to import the files. Sometimes 3rd party software does not write data in the correct Excel format.To resolve, re-save the file in Microsoft Excel. It should now be correctly imported in Designer.

How To: Input a Specific Excel Range

Another functionality of Alteryx Designer is the ability to input a data subset of an Excel file. This comes handy when working with large data.

The procedure to input an Excel range would depend on the Designer version.

Prior to 2018.1

In this example, our data starts in row 5 and column B and ends in row 7 and column D.

im1.png

  1. Bring the Input tool into your module, then browse to the particular sheet in your Excel file that you wish to pull data from. It will look like the following image. Notice that Option #3,Table or Query, points to‘Sheet1$’, we will modify this to point to our data range.

    im2.png


  2. To edit theTable or Query, click on the button with three dots () on the right side of Option #3.

    in3.png


  3. Click the SQL Editor button and change the range to

    SELECT * FROM 'Sheet1$B5:D7'

    in4.png


  4. Click OK. Now click on theUpdate Samplelink In the Input Tool properties window to see the new range.
    im5.png

2018.1 and later

  1. In versions more recent than 2018.1, the button with three dots () on the right side of Option #3 cannot be used anymore.

    img1.png

    Instead, click into the file path field, and edit it.

    2019-04-22_11-10-31.jpg


  2. Use the following syntax filepath.xlsx|||'SheetName$RangeCell1:RangeCell2'.


    For example:
    \sample.xlsx|||'Sheet1$B5:D7'


    imports the Excel file sample.xlsx from Sheet1, and the range B5 : D7. Note that the value of 3. Table or Query has changed.


    2019-04-10_16-48-35.jpg


Additional Resources

Fun Fact

Bananas grow on plants that are officially considered a herb since the stem does not contain woody tissue.

Attachments
Comments
tom_montpool
12 - Quasar

You can do this from the Excel end as well by setting a range of your spreadsheet as a "Named Range" (highlight part of your spreadsheet>right-click>Define Name...). The named range then shows up in the "Table or Query" selection list in Alteryx. Print Areas work as well, but Named Ranges are more flexible. Using Named Ranges is great because you can change the range definition in Excel (use the Name Manager on the Formulas ribbon) and your Alteryx workflow still works.

simpucca
6 - Meteoroid

Great tips!! These methods can be a lot quicker way to choose specific ranges from messy Excel files than using a combination of Select records tool with Select tool etc..

levell_x_dunn
10 - Fireball

I have found that the pulling and writing to defined names only works with the xls format or xlsx legacy format. the New microsoft xlsx format and xlsm format, IMO, doesn't allow for the defined names or keeping data formated in excel and just replacing the data. If you run into any problems, I would suggest downloading the legacy drivers from Microsoft.

geofcanuck
5 - Atom

I am doing this with the Input Data tool. The cell that I am reading in to the workflow has more that 255 characters and the input data tool is reading in only the first 255 characters. Any ideas? Thanks.

JessicaS
Alteryx Alumni (Retired)

Hi @geofcanuck,

 

Can you post a sample of your data and workflow?

 

Thanks,

geofcanuck
5 - Atom

Thanks for asking. I have created a workflow. How do I add attachments?

JessicaS
Alteryx Alumni (Retired)

Since this is a KB article, I think the attachments are disabled.  I'll follow up with you directly by email and we can post the solution here

 

Thanks,

Inactive User
Not applicable

Is the SQL editor for Excel still available?

I would like to read a specific range in a sheet from the Input tool, I have the latest version of Alteryx and it seems unavailable.

BPurcell2
9 - Comet
D12monkey
8 - Asteroid

Sorry to highjack the tread but my alteryx comes up like so when I click on the "..." in the input tool:

Table-Query selection.PNG

 

 

Is this a because my company makes me run an non-elevated version of alteryx? Could it be that my company has disabled the query tool from alteryx?

 

I also can not enter a command directly into the line on the input tool.

 

patrick_digan
17 - Castor
17 - Castor

@D12monkey  see the comments on this product idea. They removed/changed the way you can query select cells from excel. You'll just need to always type into the file input box something like this:

C:\Data\File.xlsx|||`Really Long Tab Name$A1:A2`
D12monkey
8 - Asteroid

@patrick_digan Awesome, forgot about hardcoding into the file input line. Nice way to bypass the limitations I have. THANKS!

Jamesrich
7 - Meteor

Is there anything that expands on this in terms of importing specific cells not just cell ranges? In the section 'How to: Input a specific cell range' - could this just be applied to a single cell? 

MustaphaKossai
5 - Atom

Very usefull tips !

cindy4data
5 - Atom

Exactly what I'm looking for. Marked!

Caterina19
5 - Atom

Hello

 

I have one file and I need to import all the sheets which contain "USD" in the tab name. These tabs have the same schema ( the rest of the tabs no..). What is the best way to do it? I thought about importing the sheet name list and then applying a filter on USD but now I am kind of stuck.

thanks

caterina 

svm
6 - Meteoroid

Hi Michael,

 

Thanks for your contribution - I used your workflow "How To: Import multiple worksheets from multiple Excel files" and the macros from above, but struggle to add the filenames and sheetnames as separate columns to the final output. Any good idea?

 

Thanks, Sven

rnair
5 - Atom

Hello Michael,

 

This has been truly helpful, and i am using this currently for a mundane task that i was able to automate with your solution.

 

Is there a way in which we can also include the file name in the output column using this Macro (

stuabs19
5 - Atom

The file field in the Update Value does not change for me after I type in to "Replace Specific String" (step 5). Anyone know how to get around this?

stuabs19_0-1625678729381.png

 

c_s_f
6 - Meteoroid

@MichaelAd 

 

I am following the instruction on your "multiple excel sheets one file" workflow, but i seem to only be able to pick up the info from my first sheet. Are you able to help and see what it is i am missing?

 

c_s_f_0-1639761725058.png

 

 

c_s_f_1-1639761738916.png

 

 

c_s_f_2-1639761753236.png

 

 

c_s_f_3-1639761791007.png

 

jsilavong
8 - Asteroid

@MichaelAd 

 

Hi Michael, 

 

Do you have an example of the workflow where you imported an excel file with multiple sheets then divided each sheet into a separate file?

Ekmi9
8 - Asteroid

I am not able to fetch all data with above Marco due different number of each sheet .

 

can someone help on that to configure that this as well on this macro??

 

Thanks in advance!

 

Enclosed is the log ss. 

 

Ektaoza9_0-1656587626058.png

 

simasarry123
8 - Asteroid

Option 2 - Will this also work if the data from my sheets from different excel files does not start from row 1 but instead row 17. i just have to update the configuration to import data from Line 17?