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.
Thanks for much for sharing this solution. I need to know one additional thing. How do I add the file's name while importing the data in this case?
I have multiple excel files in the same folder. Each file has 2 sheets each with the same name & structure. I am using the formula tool as you suggested entering the sheet's name. I want to include the file's name as well in the collated data so I can track what data came from which file. Please help.
If the sheet names are different across the excel files, then the most foolproof method is to take advantage of the "read a list of sheet names" option within an input tool.
Use either a wildcard or the directory tool to ensure that you get every file in the folder, and when prompted, choose the sheet name option on the input tool. Also ensure that you have the "Output File Name as Field" option set to 'Full path'.
After the input tool, the easiest route is a formula using the replace function to swap out the query string for the sheet name with something like:
Replace([FileName],"<List of Sheet Names>",[Sheet Names])
At that point you should be able to push to a dynamic input tool and not need a macro.
If you need a workflow mocked up just say so. Good luck!
Doing it this way ensures that if a sheet name changes you will be able to adapt.
In my scenario I have a number of files that have a differing number of sheets within them, each appended with a number ("Page_1","Page_2", etc). However, the data on each sheet is the same schema. It's split due to row limits on excel files.
I use the method outlined in order to ensure we get every sheet for each file, and then union the entire dataset.
@neilgallen thank you for posting. I have a related question on loading multiple excel files. I am trying to complete the Weekly Challenge #19 (https://community.alteryx.com/t5/Weekly-Challenge/Challenge-19-Excel-Record-Locator/td-p/36748) but am having trouble connecting the input tool to the file path that contains the multiple excel files I am trying to read in. I am following the guidelines of using the file path and replacing the common naming of the excel files with the wild card "*". However, I keep running into a couple error messages "Root element is missing" and a "Windows Error: Access is denied". Have you ever run into these errors before?