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

[Garrett]

Can you get away with simply using wildcard(s) in the Input Data tool's "Connect to a File or Database" configuration?

Asterisk (*) = any number of wildcard characters (in this example it replaces the unique time stamps)

Question Mark (?) = a single wildcard character

No need to mess with the "Table or Query" configuration because the sheet is named the same in every file.

I like to set "Output File Name as Field" to "File Name Only" in this scenario so I know where each row came from.

[brad_j_crep]

I'm sorry, I knew I didn't write that right.  Data is coming from 9 separate files with different names where one sheet in each file(excel workbook) has the same name.  Kind of like 'work file 01 2017|master_file, work file 02 2017|master_file, bobs work file 03 2017|master_File" and etc.  So the wildcard wouldn't work for me.  Thanks though.

Brad

[jrgo · Accepted solution]

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!

[brad_j_crep]

There was a different schema and I unaware of the interface designer 'wrench' setting to Auto Configure by Name (Wait Until All Iterations Run).  Works good now.  Thank you @jrgo and @Garrett!  Appreciate the help!