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 ,

In the configuration window of your dynamic input tool, make sure that you change option 5 so you output the full path as a separate field.

That will output the filepath in a separate field, which would look like this 

The you can go on and parse the filepath field so you only keep the month and year, and convert it to a date

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

Regards,

Angelos

[KdnD]

@AngelosPachis Thank you for your help! That was way less painful than I had imagined. 🙂

[Qiu]

@KdnD 
Maybe something like this?

And the dateformat 11/30/2020 will cause issues sometimes, do you accept other format?

[KdnD]

Hello @AngelosPachis ,

I used this workflow on another workbook but is running into the different schema error for the worksheets within that workbook. I attempted at the batch macro approach but is not getting much luck. Any advice?

[AngelosPachis]

Hi @KdnD ,

The batch macro is the way to input multiple files of different schema. How can I help?

[KdnD]

@AngelosPachis Would it also work on multiple sheets with different schema within the same file? Or I would need to have the sheets saved as separate files to run the macro?

[AngelosPachis]

Yes @KdnD , it would also work on multiple sheets from the same file. 

What you have to do is to read the different sheet names in your input tool and feed that to the "?" anchor of your batch macro - your control parameter.

Then, inside your batch macro, you will have to connect your control parameter to your input tool, and in the action tool select to replace the specific string containing the sheet name.

Now your batch macro will run for each of the different sheets in your file.

Hope that helps,

Angelos

[KdnD]

@AngelosPachis Can you point me in the right direction? I'm missing a step or two here.

[mceleavey]

For those of you struggling with the Schema issue, and for those who deal with multiple Excel sheets with varying names you can use my macros.

One loads in the sheet names, and one dynamically loads in the sheets.

I've attached an example as well.

Hope this helps.

M.