Macro - Multi Files in Various Dirs

knnwndlm

Hi SME,

I have to pull in different files from various locations. Here's an example of the files in various locations:

FY2019 Folder

DataFile1_2019

DataFile2_2019

DataFile3_2019

SourceFile1_H1_2019

SourceFile1_H2_2019

SourceFile1_H3_2019

FY2020 Folder

DataFile1_2020

DataFile2_2020

DataFile3_2020

SourceFile1_H1_2020

SourceFile1_H2_2020

SourceFile1_H3_2020

All the DataFile* have the same structure. Same with all the SourceFile*. What I want to do is to consolidate all the DataFile* into one file. Same with all the SourceFile*.

I can do this the long way but it's rather clunky since I have a lot of files to consolidate. I'm trying to build a macro to do so, but I'm being limited by the file name. In the above case, I would have to do four macros - two for each year and within each year one for DataFile* and the other for SourceFile*.

How can I build a macro to look into the FY2019 and FY2020 folders and being able to consolidate all the DataFile* into one DataFile and all the SourceFile* into the other SourceFile?

I'm new at Macros. Appreciate your help.

Thanks,

Macros

Accepted answers

davidskaife

Hi @knnwndlm

This will give you the basics to help you on the way; I've pulled together a simplified method for you to follow:

Directory Tool Input - point it to the folder above the FYxxxx folders are, tick 'include subdirectories', and search on wildcard and extension - *.xlsx

Filter Tool - filter in where 'FileName' contains 'DataFile' and also in a separate filter where it contains 'SourceFile'

Dynamic Input Tool - use one of your files as your source template, and ensure you select 'File Name Only' under option 5

Under 'Read a list of data sources' you want field to be 'FullPath' and Action to be 'Change Entite File Path'

Finally write the outputs to a new excel file!

Workflow attached. NOTE that this method will ONLY work if the files have the same structure as you have indicated. If they differ then the Dynamic Input tool will need to be replaced with a different method. This article has more info on reading in different types - https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/ta-p/20480

Hope this helps!

Dynamic Input.yxzp

davidskaife

Hi @knnwndlm

If you only have one sheet per file it shouldn't matter what they are called, are you experiencing errors? You only run into problems when there are multiple sheets.

All comments

davidskaife

Hi @knnwndlm

This will give you the basics to help you on the way; I've pulled together a simplified method for you to follow:

Directory Tool Input - point it to the folder above the FYxxxx folders are, tick 'include subdirectories', and search on wildcard and extension - *.xlsx

Filter Tool - filter in where 'FileName' contains 'DataFile' and also in a separate filter where it contains 'SourceFile'

Dynamic Input Tool - use one of your files as your source template, and ensure you select 'File Name Only' under option 5

Under 'Read a list of data sources' you want field to be 'FullPath' and Action to be 'Change Entite File Path'

Finally write the outputs to a new excel file!

Hope this helps!

Dynamic Input.yxzp

knnwndlm

Thanks @davidskaife! Appreciate it!

knnwndlm

Hi @davidskaife,

How do I modify the Dynamic Input to allow for tabs with name variation? For example, DataSource1 has the dataset named Source_2016, DataSource2 Source_2017, and DataSource3 Source_2018. If possible, I would prefer this dynamic solution than renaming the tab in each Excel file.

Thanks,

davidskaife

Hi @knnwndlm

If you only have one sheet per file it shouldn't matter what they are called, are you experiencing errors? You only run into problems when there are multiple sheets.

knnwndlm

Hi @davidskaife,

I do have one sheet per file with different names all starting with the same references. I didn't know about multiple sheets because that's where I experienced the error. I thought that as long as I selected one sheet then it should be fine. Thank you for pointing that out.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335