MACRO - Any Suggestions?

[suby]

what I'm trying to achieve is I have two excel files ( Two months Data) with two tabs with different schema and I want to combine all these into one single Dataset which can be done by creating the Nested macro mentioned in the post below by Andrew.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Read-in-Multiple-Excel-Files-with-M...

The solution By Andrewl combines the Data set when we run the workflow based on different schema with multiple tabs and Multiple sheets.

What I'm looking for is still I want to combine two files with different schema and I should be able to filter for specific worksheet in my main workflow and then do combine the data set on my main workflow say.

I'll leave this open for the community geeks to see if any one can provide a solution.

[suby]

Thanks sorry again.

It would be great if you help with the queries on the attached word Document.

Many Thanks for your time.

[mceleavey]

Hi @suby ,

You don't need to configure anything.

Click on each of the macros in the workflow, go to the "Questions" tab and select "FullPath".

Do this on both.

That's it.

[suby]

Hello,

Thanks again but when i select that i can't see any drop down which gives you to choose the FullPath.

It happens in both the macros.

[mceleavey]

Hi @suby ,

I don't know why you're in the macros. Close them! And don't save any changes!

Open the workflow I sent you. Click on each of the macros and set the parameter as previously mentioned.

That's it.

This is what you need.

Click on each of those tools and go to the "Questions" tab.

Set each one to be "FullPath".

That's it.

You don't need to do anything else.

M.

[suby]

Hello,

Sorry My bad..

Even in the workflow I cant see the Fullpath coming as a dropdown.

[mceleavey]

No problem, we'll get there.

Run the workflow. Then select the dropdown.

Ensure you've connected them up, of course. I can see you haven't connected the directory tool up to the first one (into both input nodes) and then the output of the formula  into both inputs of the second tool.

[suby]

Hello,

Tried again as suggested still no luck..

Do i need to configure the dynamic input on my macros ?

[suby]

Hello,

Hurray and it works thanks and and how can I filter for a specific worksheets.

Say if i need sheet 1 from both the files ( file1 and File 2) to be combined ? what's the approach

[mceleavey]

Hi @suby ,

Excellent news!

I've attached an updated version of the second macro which now passes through the sheet names. You can then filter on the sheet names and add a cleanse tool to remove null columns. That will do it.

M.

[suby]

Many Thanks for your time and solution and i have one two things.

I added the recently modified macro as suggested but if you look at the screenshot below after the Macro when I filter for sheet name =' PROD' I would expect to see the columns  which are from  PROD worksheet on both the files in the browse tool - 

PROD ID

REGION

FILE NAME

FULL PATH

SHEET NAME.

Why the columns ID , COUNTRY AND REG are coming through is there a way to avoid these  columns on my output other than using the Cleanse tool.

Secondly would you help me  to understand both the Macros and the workflow Please as it will give me a good understanding