Read multiple sheets and append sheet name to a new column for the data on that sheet

[KdnD]

Hello,

I'm a newbie to this and would like to know how I can read multiple sheets and use the sheet's name as a new field on the combined output.

We have a compliance due date file where each sheet represents the data for the month and the sheets are named November 2020, December 2020, January 2021, etc. The data format and headers are the same for the sheets. I was able to use the dynamic input tool to read the sheets and combined the data. However, I would like a have a "period" column where the data from the sheet will be labeled with the sheet's name (i.e. data on the November 2020 sheet is on col 1 - 5 and col 6 will have November 2020 for the period.) Then converts this period into a date format (November 2020 --> 11/30/2020).

Any help and suggestions are greatly appreciated!

[AngelosPachis · Accepted solution]

Hi @KdnD ,

You were already in a very correct direction, your batch macro was almost ready but it just needed a few tweaks.

So outside of your batch macro, you have correctly used an input tool to read all the different sheet names from a file and set that to feed in your control parameter of the batch macro.

Now, inside your batch macro is where I had to make a couple of small changes; As you can see in the screenshot below, in your batch macro the input tool was again bringing in a list of sheet names.

To make the batch macro work in your favor,  you should change that to bring in the actual data from one of the sheets. In this example, I've used April 2020 but it doesn't really matter which one you choose. So now you can see the annotation of the input tool reading "April 2020". That will allow you to read that file for April 2020, but also in the action tool, it will allow you to replace a specific string from the full path with whatever comes in from your control parameter (the list of your different sheet names).

So since this is the list of sheet names

your macro will run firstly for November 2019, replacing April 2020 in that file path with November 2019 and reading that tab. Then the second time will run for December 2019, outputting the table for that tab and so on until it reads all 13 different sheets,

Now the final thing you have to take care off is how those different tables would be outputted. Similar to a union tool, from your interface designer you can select the output mode to auto configure by name or position. If you set this to auto configure by name, that will read the field names for each one of the different sheets and stack them on top of each other based on that name.

Finally, I removed everything after your batch macro, so now everything was clean and tidy and you can see the output of all your different sheets.

Hope that helps, let me know if you have any questions on the above.

Regards,

Angelos

[KdnD]

@AngelosPachis  Thank you for the detailed explanation!!! This is awesome.