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

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!

Workflow

Tips and Tricks

Date Time

Transformation

Accepted answers

AngelosPachis

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

example.yxzp

AngelosPachis

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.

original batch.png

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).

amended macro.png

So since this is the list of sheet names

Screenshot 2020-12-30 091249.jpg

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.

final step.jpg

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.

Screenshot 2020-12-30 091802.jpg

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

Regards,

Angelos

example_v2.yxzp

All comments

AngelosPachis

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

example.yxzp

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?

1219-Kdnd.yxzp

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?

Riclets

@mceleavey: your "Multi Sheet Import Example.yxmd" macro worked beautifully. It merged 36 files with multiple sheets and multiple schemas into one output. You won't believe how much time I searched for something like this. It's a lifesaver. Your macro is the simplest of them all others. Thank you.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703