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

input multi excel folder with different name on tabs

Chunbin
6 - Meteoroid

Hello

 

I have 100+ excel folder that need to combine into one. I only need the data in the first tab. but they are named differently, some of them named as sheet 1 and some of they named as creater's name etc.

 

I am try to change the database link address as the following: W:\Data\Chunbin\Expenses project\*.xlsm with a * at the end.

 

Then select the tab name, for example sheet 1. So only the first tab that has been named as sheet 1 been inputed and rest of them are not.

 

is there anyway i can input all the excel data even the tab name are different?

 

also see the attachment.

 

Thanks

 

Chunbin

6 REPLIES 6
JordanB
Alteryx
Alteryx

Hi @Chunbin

 

Here is a sample macro which will read in a list of files from a folder. 

 

Configuration

 

When you import the package you will have a module which looks like the screenshot below. You need to go file>>Save As>>

 

Once you save the macro you can open a new canvas and insert the macro by right clicking on the canvas>>Insert>>Macro

 

You then need to use a directory tool to read in the files from a folder.

 

onfigure the macro by coosing the full path field.

 

Inside the macro

 

You can then insert the macro which contains two parts:

 

Part 1: This will read in a list of the sheets and the dynamically take the first sheet and recreate the file path needed for the next macro

 

Part 2: The second macro will then take the file path and read the file in, regardless of if the excel files have different field schemas or not.

 

Pic 1.png

 

The macros were created in Alteryx Designer 10.6.

 

To understand the batch macro process more please refer to the following articles:

 

Batch Macro: An Example 

Reading in files with different field schema

 

Best,

 

Jordan Barker

Solutions Consultant

Chunbin
6 - Meteoroid

Hi Jordan

 

Thank you for your reply, I just tried with your model. it works to catch one excel file and the tabs within that file. but it does not upload result of the excel files?

 

Thanks

Chunbin
6 - Meteoroid

Hi @Jordan

 

For those that have different tab name as the file i have chosen originally failed to input. also it is selecting the xlsm. but i also have xlsx.

 

Do i need to do they seperately?

 

please find the attachment.

 

Thanks

JordanB
Alteryx
Alteryx

Hi @Chunbin

 

I have split the workflow into the three separate parts to make it easier to see and understand.

 

Complete Workflow

 

Overview.png

 

 

Part 1

  • The Directory tool will be used to browse to the folder which has your xlsx files in. 
  • The wildcard option in the configuration window can be set to *.xlsx and the in the repeat workflow *XLSM
  • You will have to two workflows for the XLSM and XLSX files. You can then union the two workflows together afterwards

Part 2

  • The formula tool adds "|<List of Sheet Names> to the end of your excel file paths. This is needed because we want to dynamically read all the sheets from a file and choose the first one.
  • This is where the macro comes in (First solid blue tool). Inside this macro it reads all the sheets then using a recordID, filter an formula tool dynamically created the correct field path. 

Macro 1.png

Part 3

  • The next macro (second solid blue tool) then reads the file paths and unions the data regardless of if the fields match because of the auto-configure by name' setting in the interface designer. 

Macro 2.png

 

You then have a workflow which dynamically reads multiple XLSX and XLSM (Once you modify the workflow to take xlsm files), takes the first sheet and then unions all the data. 

 

The workflow attached should run with the directory tool and two macros working, so you should be able to see the complete solution.

 

Built in Alteryx 10.6

 

Best,

 

Jordan Barker

Solutions Consultant

AndrewCrayford
8 - Asteroid

Hi @JordanB

 

How do we amend the workflow to which tabs it imports?

JordanB
Alteryx
Alteryx

Hi @AndrewCrayford

 

In 'Part 2' the image shows a filter tool which reads in the first file. Here you can change the logic to read which sheet you want.

 

Best,

 

Jordan

Labels