Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

Lakshmi11
5 - Atom


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.

7 REPLIES 7
BrandonB
Alteryx
Alteryx

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
5 - Atom

Thank you so much for the solution

sangpom33
7 - Meteor

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
Alteryx
Alteryx

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
7 - Meteor

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

sangpom33
7 - Meteor

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

BrandonB
Alteryx
Alteryx

@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. 

Labels