MERGING SEVERAL TABS TOGETHER - 100+

Hi All,

I am attaching a file that has around 5 tabs as an example to merge. I have tried using Dynamic Input and have had success in merging 20-30 tabs together.

However, just wanted to see how can I merge more than 100 tabs all together? Sometimes the Dynamic Input gives a Schema error.

Any solution would be appreciated. Thanks!

File.xlsx

Workflow

Datasets

Developer

Input

Reporting

Settings

Error Message

Regex

Output

Custom Tools

Parse

Macros

Connectors

Common Use Cases

Server

Best Practices

Transformation

Bug

Apps

Batch Macro

Help

Fuzzy Match

Dynamic Processing

Join

Preparation

In Database

Predictive Analysis

Machine Learning

Database Connection

Scheduler

Documentation

Power BI

Iterative Macro

In-Database

Accepted answers

Maskell_Rascal

Hi @iamviraj13

Here is how you can do it.

You'll first want to read in your Excel File to include the Full Path as a field and also only read in a list of sheet names.

The use a Formula tool to create updated FileNames that include the full path and the sheet name.

This will then be fed into a batch macro that will read in and combine all the tabs.

The macro itself is configured for the needs of the sample file you provided, so the template it uses starts the data import on row 9 but can be changed if needed. You can also include the Full Path or File Name as a field, so you know which tab the data is coming from.

Attached is a zipped copy of the sample workflow and the macro for you to try out.

Let me know if this works for you.

Cheers!

Phil

Input All Excel Tabs.yxzp

All comments

iCFO

Have you looked into this post?

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets-or-a-Specific-Excel-Range/ta-p/398220

I also sometimes use VBA to do things like splitting tabs to separate files, renaming tabs or combining tabs of the same schema to simplify multiple tab management.

https://www.exceltip.com/cells-ranges-rows-and-columns-in-vba/consolidatemerge-multiple-worksheets-into-one-master-sheet-using-vba.html

Maskell_Rascal

Hi @iamviraj13

Here is how you can do it.

You'll first want to read in your Excel File to include the Full Path as a field and also only read in a list of sheet names.

The use a Formula tool to create updated FileNames that include the full path and the sheet name.

This will then be fed into a batch macro that will read in and combine all the tabs.

Attached is a zipped copy of the sample workflow and the macro for you to try out.

Let me know if this works for you.

Cheers!

Phil

Input All Excel Tabs.yxzp

iCFO

Great macro solution @Maskell_Rascal ! I am definitely going to be using this myself as well.

iamviraj13

Thanks, @Maskell_Rascal for the detailed explanation.

I have multiple excel files each having singe sheet named as MOD_IVA. All these excels have similar columns, some have extra unique columns too. I want to collate all these columns into on single excel file. How do I do it. Have tried macro batch input but getting an error of different schema.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703