I would recommend either using ETL software to pull the file name or sheet name into the data source, or you can use custom SQL with connecting to a single Excel workbook, and UNION ALL the sheets together, or create a script that does the work for you.
I could see a VBScript looping through all the Excel files in a directory, grabbing the file and sheet names, and stacking the data together with an extra column for date pulled from file/sheet name into a CSV.
Thanks. We currently don't use any ETL software -- are there any free or open source ones? I was thinking that there had to be a way to do this inside Tableau -- using the Excel filename or Excel worksheet tab name as a Dimension.
I do not currently know of any open source ETL software that can do what you are looking for, point it at a folder and read in file names and sheet names without setting up the metadata.
I would recommend a scripting language, I personally know what you are looking for can be done in VBScript, and I am sure others know if this can be done in Perl or Python or something else.
OK, will check out VBScript. It'll need to parse the Excel filename or tab name and then insert the text into a spreadsheet column so Tableau will then have a date column to read. I was hoping there would be an easier way but maybe not.
a quick google search revealed an excel calculation that you could insert as data into your excel sheets. This one returns the workbook name:
You can infer from the above that the name is being stripped out from the full path, which by the way is:
the web page I found for this was: