Fetch out information from many tabs in a excel sheet

ErikH

Hi,

I have an excel file with tons of tabs in it.  Is there any way to do this:

1. Fetch out the articles below column A: "SKLU"  and coulmn K, "Price 6"

2. Inject the tab-name into a new coulm next to the two coulmns that are fetched out from point 1. 

3. Have a rename rule due to bad naming setup: for example: "campaignweek3", "Campaign week 1" and "campaign week2" should be renames to a master setup to: Campaign Week X (where X is the week nr)

I've attached a dummy file with the same setup

In advance, thanks for all help!

Book3.xlsx

jdunkerley79

Yes this is possible.

Start by reading in the list of sheet name using the Input tool.

In the Input tool make sure you tick to keep the entire file path in the output.

You can then use a formula tool to make a new path with the sheet name at the end. It needs to be a specific format like:

C:\Users\jdunkerley.SCOTT\Downloads\Book3.xlsx|||`Campaign week 1$`

You can then feed this to a dynamic input tool. If you configure the sample to include the full path then the sheet will be in the results.

A final Regex can extract the sheet.

Sample attached

Dynamic Sheets.yxmd