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
Solved! Go to Solution.
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.
The macros were created in Alteryx Designer 10.6.
To understand the batch macro process more please refer to the following articles:
Reading in files with different field schema
Best,
Jordan Barker
Solutions Consultant
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
Hi @Chunbin
I have split the workflow into the three separate parts to make it easier to see and understand.
Complete Workflow
Part 1
Part 2
Part 3
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
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