Macro for Combine multiple excel files from a folder into one removing duplicates

[Lakshmi11]

Hi,

I have around 30+ files with same 3 columns that needs to be merged after removing duplicates using a macro rather than pulling up up all files in workflow canvas and combining them using Union tool.

Is there a way to do this using Dynamic input tool or  batch macro?

I have attached my current workflow created for reference. please help.

[BrandonB]

Absolutely! You can use a directory tool to pull all of the file names from the folder that you want to bring them in from, then feed that into a dynamic input tool using one of the files as a template and reading in the Full Path as the data field and "Change Entire File Path" in the action setting. This will effectively pull all of the files from that folder together into one stacked data source. 

Edit: You will also want to use a formula tool to update the file path with the sheet name just prior to the dynamic input tool.

Formula with FileName as the output column

[FileName] + "|||VincentReport$"

This way it will always pull that sheet name from the files being brought in

[Lakshmi11 · Accepted solution]

Thank you so much for the solution

[sangpom33]

I could not get this to work.  It give me the error "No Sheet specified, you must specify a sheet" and i did specify the sheet in the Formula tool as well as the "Input Data Source Template"

[BrandonB]

Hi @sangpom33 you are probably really close. Take a look at the last part of my previous comment:

 You will also want to use a formula tool to update the file path with the sheet name just prior to the dynamic input tool.

Formula with FileName as the output column

[FileName] + "|||VincentReport$"

This way it will always pull that sheet name from the files being brought in. Your filename will look something like

C://users/yourusername/Desktop/filename.xlsx|||sheet1

This path is what needs to feed into the dynamic input.

[sangpom33]

OK, got it.  I had to change [FileName] in your formula to [FullPath], then it worked

[sangpom33]

What if the sheet names are different?  Is that an easy fix to your macro?

[BrandonB]

@sangpom33 That makes sense. I said file name when I really meant full path, so glad you caught that. If you are wanting to pull in different sheet names it may be easiest to use a macro that someone else has already built. 

Although they are not created by Alteryx, the Crew macros (http://www.chaosreignswithin.com/p/macros.html) are pretty awesome. Otherwise you will need to build in logic to get the appropriate sheet names hooked up to their respective file names. Not impossible, but might not want to reinvent the wheel given that people have created macros to do exactly that.