Reading multiple .xlsm files at once

[Tienemientje]

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

[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

[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

[DavidP · Accepted solution]

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

[Tienemientje]

Thank you very much.  I found a similar workflow in other post, but the formula to add the sheet was not present in those.  And that is key to making it work for me.  

Kind regards, Tienemientje