how to read multiple sheets from multiple files in excel at once

Question

Hi team,

I have 6 excel files. Each file has multiple sheets. I want to read all excel files with all the sheets it has.

example:

file 1 (with 4 sheets).

file 2 (with 3 sheets)

file 3 (with 5 sheets)

file 4 (with 2 sheets)

file 5 (with 1 sheets)

file 6 (with 7 sheets)

note:all sheets has the same data with 4 columns  (column names are different but with the same data)

in some sheets it has "customer ID" but in some sheets it has just "ID" but in some sheets it has "USER ID"  but all represents the same.

please guide me on this.

Thanks in advance.

rag329 · Answer

Thank you so much team,  @FinnCharlton @FrederikE

FinnCharlton · Answer

Hi @rag329, have a look at this example workflow. Here we use a batch macro to deal with the different schema of the tables:

First, we use the Directory tool to list all Excel files in the folder we want to read.

We then use the Dynamic Input tool to list every sheet inside each of these files.

The last formula tool forms a unique path for every sheet inside every Excel file that we want to read. We then pass these into a batch macro which will read them all:

This simple batch macro reads in each sheet one at a time, then unions them together. Importantly, we choose the 'Auto Configure by Position' option in the Interface Designer to deal with the different column names. I've attached the workflow below - hope it helps!

Import multiple sheets with different schema.yxzp

FrederikE · Answer

Hey @rag329,

This article will help you with it: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/Read-in-Multiple-Excel-Files-with-Multiple-Tabs-that-have/ta-p/51145.

Regarding the Different ID Names - I would just merge them together afterwards using a formula tool (e.g. Field A + Field B + ...)