Import Multiple Excel Files with Varying Worksheets

[crunyeon]

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.

[JordanB · Accepted solution]

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]

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]

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.

best,

Jordan

[parnell]

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"

But the macro continues to error out.

what am I missing?

[JordanB]

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]

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]

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!

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

Best,

Jordan

[mcafebri]

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]

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