Reading multiple .xlsm files at once

Hi all,

I have found a number of topics on reading multiple excel files, but either they are not quite the same as what I want or they are so difficult it is hard for me to understand as I'm quite new to the program.

I have let's say 3 excel files (.xlsm) or more which change on a regular base. Each of the files has 5 tabs. The files have the same layout.

On sheet 1 I have customer data. Sheet 1 has the same column headers in each of the files.

On sheet 2 I have country data. Sheet 2 has the same column headers in each of the files. But of course different info then sheet 1.

Sheet 3 is about costs.

What I want to do: Put the 3 (or more) excel files in a folder. Alteryx should now read "sheet 1" of each excel file and combine this info. I then want to perform some Alteryx magic on that information and write this to an excel file (I can do this part).

Let us say in a different part of the flow, I want Alteryx to read "sheet 2" of each excel file in the folder and combine the info. Again I want to perform some Alteryx filters/formulas and in the end write this to a different excel file or different sheet.

I do not want to read each file seperately and pick "sheet 1". Alteryx should read all files in the folder as it varies how many files I get.

Is this possible?

Thank you.

Kind regards, Tienemientje

Input

Accepted answers

DavidP

In that case you need to use a batch macro to load the files, like this:

multiple excel files sheet1 macro.png

multiple files sheet1.yxzp

All comments

DavidP

If the files really have exactly the same structure in each sheet, you can use a normal input tool to load, say, sheet1 of the first file and then go an edit the filename and replace all or part of the filename with an *, leaving .xlsm|||Sheet1$ in place, like this, which will then load all the files in the folder that meet the wildcard criteria and union the results. If you want an identifier that shows which file the rows come from, output the filename as a field

multiple excel files sheet1.png

Tienemientje

Hi @DavidP ,

Thank you very much. I was also still trying and I found this. However it gives an error "file.... has a different schema then the first file in the set and will be skipped". To me all files in my input folder look the same. Users have to start from the same template. The only thing that can cause this is that not all columns are filled in for each row (or sometimes even in a sheet). Let us say I have a column called "Streetname". If a user does not want to change any streetnames, he will leave that column empty in that sheet. While another file can have that field filled in in one of the rows.

I tried to put everything to "text" in excel (which should not be done because one of the tabs is about costs), but that does not seem to make a difference.

Kind regards,

Tienemientje

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703