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 Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Import Multiple Excel Files with Varying Worksheets

crunyeon
7 - Meteor

I am looking to import an entire folder of Excel documents into a workflow without having to add a new input tool for each file.  I tried using a wildcard in the Input field, but I am having a problem.

 

All of the worksheets within the Excel files are named the same, however they don't all exist in each file.  So for example, they appear like this:

 

Spreadsheet 1

     West Region Sales

     North Region Sales

     South Region Sales

 

Spreadsheet 2

     West Region Sales

     East Region Sales

 

Etc. 

 

Can I perform an If statement on the import that just ignores the excel file if it doesn't have the appropriate worksheet?  Thank you in advance for your help.

 

 

11 REPLIES 11
JordanB
Alteryx
Alteryx

Hi @crunyeon

 

I have attached a workflow built in Alteryx Designer 10.6 which has two batch macros. 

 

1. The first macro creates a list of all sheets within all excel files

 

2. The second macro reads each of these files paths (with sheet names) and reads the data into Alteryx.

 

You will need to update the directory tool and make sure you are reading in xlsx files as that is what this workflow is configured to take.

 

This should give you the basics if you need to slightly modify for your use case. 

 

Best,

 

Jordan Barker

Solutions Consultant

parnell
7 - Meteor

Hi @jordanb

 

I'm in a similar situation - but my multiple files ONLY have one sheet (sheet1) and they all have the same layout.

 

I'm trying to modify your flow/macros - but continue to error out.

 

What do I need to change in your flow/macro?

 

(many thanks in advance...as I've been fighting with this for a while now - despite the various threads on this)

 

Parnell

JordanB
Alteryx
Alteryx

Hi @parnell

 

You will need to edit the formula tool. Instead of "<List of Sheet Names>" you just need to write "Sheet1$".

 

Once you have done this your full path field should read as an example: C:\Users\filename.xlsx|Sheet1$

 

You can then delete the first macro in the workflow and connect the second macro, making sure the full path field is selected in the configuration.

 

pic3.png

 

best,

 

Jordan

parnell
7 - Meteor

Thanks @JordanB

 

Making progress - but hung up on the second macro.

 

You say configure the macro to use the full path field.

 

I've changed the "output filename as field" and changed the "connect to file or dbase"

 

2017-04-20_1024.png

 

But the macro continues to error out.

 

what am I missing?

JordanB
Alteryx
Alteryx

Hi @parnell

 

Sorry all you need to do here is change the input tool to your .xlsx file within the macro. Then save the macro.

 

On the interface of the macro just change it to full path in the drop down menu within the configuration window.

 

Best,

 

Jordan

mcafebri
5 - Atom

Hey Jordan, 

 

I'm trying to do the same thing. I followed the steps, but I must be hung up somewhere. When I change the path to reference the single sheet (titled Order List), I get an error that each of the excel files doesn't have a sheet that matches this. Any idea where I've gone wrong here? Thanks!

JordanB
Alteryx
Alteryx

Hi @mcafebri

 

In this image you will need to keep the first macro which should generate a list of your sheet names dynamically. 

 

The error you are receiving is because 'Order list' does not exist in each sheet or it is spelt differently or has special characters in it!

 

List of Sheet Names.PNG

 

What output do you get from the first macro? do the sheet names look correct?

 

Best,

 

Jordan

mcafebri
5 - Atom

Hey Jordan, 

 

Thanks! This worked great to read in all of the files. It does seem all of the relevant sheets are correctly titled "Order List". What's the most efficient way to concatenate just those specific sheets into one output? 

 

Best,

Brian

JordanB
Alteryx
Alteryx

Hi @mcafebri

 

You would replace '<List of Sheet Names>' in the formula tool with the name of the sheet 'Order List'.

 

Delete the next macro (which would read all sheets in) and then select the field you just created with the sheet name at the end in the last macro dropdown.

 

Best,

 

Jordan

Labels