Reading in one similar excel sheet from multiple excel files

brad_j_crep

I haven't seen this in the community yet, maybe I've missed it but does anyone know how to input data from multiple excel files, differently named, where the sheet name is the same?  So I have files with dates in their name but the sheet with the raw data is named the same in every file.  I need to bring all the sheets together on one sheet but haven't been successful using a directory tool, a formula tool and then a dynamic input.  Anyone have any ideas?

Thank you in advance!

Brad

jrgo

Hi @brad_j_crep,

I believe @Garrett suggestion would still be the proper approach for what you're trying to achieve. Using the wildcards in the file path only looks at the file name, not the sheet name. However, one common issue I see (which may be the reason it's not working for you) is if there are schema variations from one file to the next. Even if it looks like every sheet has the same amount of columns and same headers, It's possible that a column that was attributed as a TEXT in the first file, but read in as a NUMBER in the next, which fails the matching schema rule.

If this sounds like it could be the issue you're seeing, best option would be to create a batch macro that can process each file separately and then combine them in the final output. What makes this option work that you're able to tell the output of the batch macro to ignore the schema rule and just output the results by their name or position (like the options you have in a UNION tool).

Hope this helps!