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!

[jdunkerley79 · Accepted solution]

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

[ErikH]

Thank you so much for helping me out!

[ErikH]

Hi again,

Any good solution to these errors and warnings I get when i try the source file;

Error; Dynamic Input (3) The file "2018.xlsx|||`Superweekend 21$`" has a different schema than the 1st file in the set.
Warning: Dynamic Input (3) The file "2018.xlsx|||`Week 42$`" has a different number of fields than the 1st file in the set and will be skipped

[JoeS]

Hi @ErikH

You can use my macro if you wish:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Input-Batch-Macro-AKA-the-Extra-Dynami...

Feel free to have a look under the hood to see what it is doing as well.

[ErikH]

Do you have a manual or such for this. Tried to test it out, but failed :|

[JoeS]

Currently that post is what I have put together for it.

If you open up the sample once extracted does that work?

[jdunkerley79 · Accepted solution]

The attached package replaces the Dynamic Input with a Batch Macro

This gets around the variable sheet column issue - it is the same as @JoeS solution just a very cut down version.

[ErikH]

Thanks again, this solved my problem!!