Single output file needed - filtering data from multiple files

[Varshaa]

I have multiple files with 2 sheets (say A and B). The goal is to create a single output file with 2 sheets containing filtered data from A and B of each file.

I was able to create a workflow with 2 macros (1 each for A and B) which read and filter data from directory. However, I am not able to create a single output file combining their outputs. To make matters worse, when I try to write an output .xls file for a single macro (say for A), I see it has results in multiple sheets - 1 sheet for each file the macro read. I want the output file to have single sheet for all A and single sheet for all B. Any help with this would be great!

Source I used for creating my macro to read input from different files having same sheet name: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/CS-Macro-Dev-Reading-in-Multiple-Fi...

Thank you.

[DanielMS]

Hi @Varshaa,

Please see the attached workflow which demonstrates what I believe you are trying to achieve.

Dan

[ashissanpui]

Here are the steps to achieve that:

1. Input all data using wild character(*). Include fullpath while importing those files

2. Filter on fullpath which contains sheet name

3. Apply all other necessary filters and output it to a file during this time use BLOCK UNTIL DONE

Hopefully this will save you.

[Varshaa]

Thank you for your reply! I'm not quite sure how to go about "BLOCK UNTIL DONE" can you please elaborate?

So while playing around, I realized when I save output from within the macro directly, I get exactly what I need (1 sheet "A" in isolation). No duplicate records whatsoever.

However, I also have another macro (for 1 sheet "B" so this is configured differently).

As I need to combine outputs of both these macros, I created a workflow to use macro A and B. I had to provide input directory again and I suppose this somehow ended up creating duplicates for me.

Is there a better way of doing this?

MacroWorkflow

[Varshaa]

Sorry for responding late! Thanks very much for your reply and sharing your workflow.

The problem I see in using that solution in my case is that you have only 2 csv files as input. I have a set of several input files with the same 2 sheets and I'm basically looking to get an output file with the same 2 sheets but having aggregated and filtered data from all input files.

I have provided screenshots of my workflow in my other reply. Would be great if you could please take a look at it and help me.

Thank you!

Varshaa

[ashissanpui]

You can do like this. 

Also if you want to set order for your data.

[Varshaa]

Thank you, @ashissanpui 

While the join did crate a few data sanity issues which I will figure out, I now see data intended for 2 sheets  A and B combined into a single sheet.  Any suggestion on how I can split these into 2 sheets of a single excel file? Or at least split them into 2 files? Thanks!

Data for sheet A and for sheet B

[ashissanpui · Accepted solution]

@Varshaa  after input app, use a formula app for new field source which will identify data stream source

then at the output file select the following,

this will create separate excel sheets. Try play with settings to get your desired output.

Ashis

[Varshaa]

@ashissanpui This was such an elegant tip! Thanks very much!!

It works great when there is no macro used. I will relook at my macro to check where the Source field is getting removed.

[ashissanpui]

Thanks Varshaa,

You can check the option Keep the field in output. Source will get carry forwarded or else be dropped after processing if unchecked.