Running Batch Macro for Two Separate Files with Same File Name

[macst245]

Hello, I have 40 input files, there are two sets of 20 files each with the same file name for the current month and previous month (I.E. there are two sets of files named File A, File B, File C, etc).  Is there a way to combine these in a macro based on the input file name, so File A for the current month would be paired with File A from the previous month?  I just need them paired together as I have the tools in place to perform the analysis once they are combined.  I would also need these outputted into one combined excel XSLX which has 20 tabs (one for each of the new output data sets, I.E. Tabs are called File A, File B, File C, etc).  Thanks so much and if I can clarify please let me know.

[rfoster7]

I'm pretty sure, if I'm following you, that you could make two directory tools pointed at the two directories you are pulling from, pull in the filenames (with directory) then run those through a dynamic input to cycle through all 40 files to bring in the data into one stream. Make sure you include the file name as a field, you would need that to differentiate the data. 

After you get it all into one stream, you do whatever you do with the data. You can use a formula tool to use the filename field to make a new field called "output filename and sheet" where your formula will be something like "c:\directory\output file.xslx|||" + [original_file_name]" where [original file name] is the FileA , FileB, etc that you cull out of the input filenames. Then when you output, you tell the output to use that "output filename and sheet" as the output filename and it will create one file with all 20 of your tabs in it. 

I would build you a sample, but I don't have your data and I don't really want to mock up 40 files of fake data. But if you can't figure it out, let me know and if you send me two zip files of test files I can build it out for you. 

[macst245]

@rfoster7 Good morning, sorry for the late reply, I have tried to do as suggested but couldn't come up with anything that worked.  I have attached two sample zip files so hope that can help, thanks again for your help with this!

[rfoster7 · Accepted solution]

Here you go: 

Some highlights: 

In the input, use the wildcard * to signify you want all the files that match that pattern to feed into the data stream

In the input, use the Output Filename as Field (FullPath) so you can use that later for determining the file and whether it is current or previous month

In the input use start Input on Line 16 to skip your header rows and get to your real column headers

I use split column to text to strip the filename from the filepath, but there are a dozen ways to do that. I just wanted to show you one.

In the output, I use "Take File/Table Name from path (change entire file path) in order to write the data to different tabs in one file output.

I've added a column in the data stream to tell you where it is previous or current month data. I feel like you will need that. 

[macst245]

@rfoster7 Thanks, unfortunately I have an older version of Alteryx and can't download this workflow. Is there a way for me to download it with the older version?  Thanks again for all your help!

[rfoster7]

There is. You can download it, then go to the yxmd file, right click and edit it with notepad or notepad++. You will see the workflow as an xml. There you can put your version number in (see screenshot) and save it. then you can open it with your version of Alteryx. 

[macst245]

@rfoster7 Thanks!  You are a lifesaver