This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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
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.