MACRO - Multiple workbooks / Multiple sheets

[suby]

HI All,

I have two Input files  as below.

January_31-01-2023

February_28-02-2023

Where I want to take the PRODUCT sheet from each workbook and combine it and produce the Output file ( attached expected OUTPUTDATA file) but the schema is different in both the files for the PRODUCT sheet.

For example purposes I'm taking only the PRODUCT sheet but in real world there are more sheets that i want to bring in and there will be difference in schema.

Could you please provide me a Macro which can handle this situation. 

Many thanks

[binuacs]

@suby The below post might be helpful for your use case, there are two batch macro attached in the post, the first macro reads all the sheet names, you can add a filter tool then filter out only the sheet you want to process 

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/Read-in-Multiple-Excel-File...

[suby]

Hi Binuacs,

Thank you so much for the Quick Reference.

Quick clarification you mean when you say first Macro is that test.yxmc Macro ?  could you please clarify where exactly to add filter and also if i want more than one sheets how to include that in that filter?

Many thanks

[binuacs]

@suby  yes, test.yxmc macro , add a filter after the input tool , add the condition SheetName=‘PRODUCT’, save macro, run the workflow after updating the directory tool with correct path

[suby]

Thanks Binuacs and when  ran the workflow  without any filters in the test.yxmc macro it picked up all the sheets and combined it and i got the output results as below

but can we get the output in the desired output as below with full path and the file name included as well.

Many Thanks

[binuacs]

@suby Here is the updated version of the workflow attached

[suby]

Thanks Binuacs for the solution and we have a use case to combine historical data with Daily snapshot Data the scenario as below.

December_01-12-2022   (File Generated 1st December But Data as of End of November 2022)
January_01-01-2023       (File Generated 1st January 2023 But Data as of End of December 2022)
February_01-02-2023     (File Generated 1st Feb 2023 But Data as of End of January 2023)
March_01-03-2023         (File Generated 1st March 2023 But Data as of End of February 2023)
April_01-04-2023            (File Generated 1st April 2023 But Data as of End of March 2023)
April_14-04-2023            Daily snapshot (File Generated 14th April 2023 But Data as of End of April 13th 2023)

I have the list of above Files in my local drive where I have combined all the Data using the Macro provided by you which works great thank you. Once I combine the all the history month end data with the Daily snapshot data which is fine. But I'm looking for say

when I run the workflow today

December_01-12-2022     (File Generated 1st December But Data as of End of November 2022)
January_01-01-2023         (File Generated 1st January 2023 But Data as of End of December 2022)
February_01-02-2023       (File Generated 1st Feb 2023 But Data as of End of January 2023)
March_01-03-2023           (File Generated 1st March 2023 But Data as of End of February 2023)
April_01-04-2023              (File Generated 1st April 2023 But Data as of End of March 2023)
April_14-04-2023              Daily snapshot (File Generated 14th April 2023 But Data as of April 13th 2023)

when I run the workflow tomorrow

December_01-12-2022     (File Generated 1st December But Data as of End of November 2022)
January_01-01-2023         (File Generated 1st January 2023 But Data as of End of December 2022)
February_01-02-2023        (File Generated 1st Feb 2023 But Data as of End of January 2023)
March_01-03-2023            (File Generated 1st March 2023 But Data as of End of February 2023)
April_01-04-2023               (File Generated 1st April 2023 But Data as of End of March 2023)
April_15-04-2023               Daily snapshot (File Generated 15th April 2023 But Data as April 14th 2023)

It has to be dynamic everyday and the daily snapshot data should be overwritten everyday (Attached a sample files)

Many thanks

[binuacs · Accepted solution]

@suby attaching the updated workflow , this workflow will pick the last 5 months data + current date's data and combine into one

[suby · Accepted solution]

Hi Binuacs,

Thanks for your time really appreciate it and the solution works great. Thanks again.