input multi excel folder with different name on tabs

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

input multi excel.yxmd

Input

Accepted answers

Chunbin

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

Multiple input.docx

JordanB

Hi @Chunbin

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

Complete Workflow

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

Full Workflow Example.yxzp

All comments

JordanB

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

First Sheet Different Schema Macro.yxzp

Chunbin

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

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

Multiple input.docx

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703