Need to read in multiple Excel files with different sheet names / mapping file involved

Question

I have tried multiple macros and cannot get this to work. It is hard because this need to be very dynamic / hands off for future runs.

Here is what I am dealing with. I have a folder that has 8 files (and in the future could have more). Each of them has one sheet that I need to bring in, and the sheet name is named after the entity ID, which is actually part of the file name. For example, file "1234_hello.xlsx" has a tab called "1234" and that is the tab I need to bring in. Each file has tons of other random tabs, so this makes it more challenging.

Another piece to this is I have a master mapping file because I need to bring in the same columns from each of these tabs, but each file has slightly different names for the columns. So I also need to know where to incorporate the mapping file. Does that go inside or outside of the macro? The mapping file is just 3 columns (Original Name, Entity ID, New Name), so that way all columns can be named the same once the data is brought in. I will need to transpose the data so that the mapping can join correctly.

Eventually I will be doing calculations with these columns, but for now, I really need assistance with setting this up. So far, I have a directory tool that is linked to my folder that has the 8 files. I then had a select tool for "fullpath". From there it got messy.

Gaurav_Dhama_ · Answer

Hi @slaurel ,

I may not be able to provide you a workflow but the idea is as below.

Read all the files using a Directory tool, pass each path into a batch macro that will read all the sheet names, now you have all sheets for all the files.

Use formula tool to pick the entity ID from the file name, use this entity ID as filter condition for your sheet names (contains([SheetName],[EntityID])).

Now you will have file path and sheet name pair. Combine them together to form a full path, for example, c:\fileName_1234.xlsx|||1234_SheetName.

Now use a Batch macro that will read these files using the path that you created in step above. This is where your mapping file also goes in. Your mapping file will rename the columns inside the macro and give out a standard output.

This way your output coming out of the macro will be uniform.

Hope this will give you some idea as to how you can get around your ask.

rzdodson · Answer

@slaurel attaching an updated workflow that will address the first part of your inquiry here to get you started.

How off are these column names as we move from sheet to sheet, workbook to workbook? That's going to dictate the approach (e.g. Fuzzy Match, Regex, Dynamic Rename) that the Community will likely take to solve the second part of your inquiry where we are renaming fields to ensure data alignment prior to the start of your calculations.

Solution.yxzp

slaurel · Answer

@rzdodson this does not work, unfortunately. I have multiple sheets with different sheet names. The mapping file is for the column names in each tab. That is 100% necessary. Thank you for attempting to help though :)