Import Excel File with Multiple Sheets and Union with Other Excel Files with similar Sheet

Hi all,

I have approx. 40 excel files (in the same directory) each with 20 sheets in them. Each file has the same number of sheets, the same sheet names and each corresponding sheet name has the same format (i.e. sheet 1 has the same format in all files, sheet 2 has the same format in all files, etc. although sheet 1 and sheet 2 have different schema).

I would like to union all the files by sheet name so that I can output one large file with all 20 sheets containing data from the 40 files. Is there a way I can do this? Any help would be appreciated!

Kind regards,

Michael

Datasets

Input

Tips and Tricks

Custom Tools

Macros

Best Practices

Batch Macro

Dynamic Processing

Preparation

Interface Tools

Data Investigation

Iterative Macro

Accepted answers

binu_acs

@MDonnel8 I updated the input file from .xls to .xlsx and generated the output using the same workflow

CombineExcel.yxzp

All comments

binu_acs

@MDonnel8 One way of doing this with the batch macro

CombineExcel.yxzp

MDonnel8

Thanks for your reply! How exactly do I set up the batch macro to suit my needs?

binu_acs

@MDonnel8 The attached macros should work for you, you need to change the path of the directory tool. Let me know if you face any issues for running the workflow

MDonnel8

I changed the directory to point towards where my files are stored and received the following output:

The output only gives one sheet and none of these columns appear in my files.

binu_acs

@MDonnel8 your input files are .xls files? What is the output from the directory tool?

MDonnel8

Yes they are, is that an issue?

Below is the output from the directory tool:

binu_acs

@MDonnel8 one question, are you able to run the attached workflow without any change and getting the same result mentioned in the image which I provided , the macro looking for .xlsx files, right now I’m away from my system, I will test with .xls files once I’m back. Would you mind attach some .xls file with dummy data?

MDonnel8

Yes the only change I made to your workflow was to the directory and this gave me the output I screenshot above.

I've attached an example xls file - if this proves to be an issue I can change the files to xlsx.

1. GDM AB000.xls

MDonnel8

@binu_acs have you gotten a chance to look at this since? This is something I would like to be able to do today - really appreciate your help so far by the way!

binu_acs

@MDonnel8 .xls file will not work with the above macros. you have the options to change from .xls to .xlsx then that would be the simple way to solve the issues. please go through the below post regarding how to process .xls files

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Input-tool-use-of-xls-file-extension/td-p/547635

binu_acs

@MDonnel8 I updated the input file from .xls to .xlsx and generated the output using the same workflow

CombineExcel.yxzp

MDonnel8

This works thank you!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335