community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More

Multiple files in folder

Highlighted
Asteroid

Hello,

 

I have multiple files in a folder with different months of data. the data is all the same style. I need help figuring out how to: ex run Jan data with workflow and than run Feb with workflow and than append the data I chose from the workflow together. I can't have the same workflows joined at the beginning because I am using the select records for certain rows. I read you can use a batch macro but I am not sure how to do that at all. Any help would be absolutely great. thanks!

Alteryx Partner

@chughes49 


This can certainly be accomplished with a batch macro, but I'd first suggest ruling out that it cannot be done by reading the files in and segmenting by file name.  You mention that this approach wouldn't work because you use the select records tool.  Which records are you selecting - how are you determining this?  Would it be possible if you selected "File Name" or "Full Path" in the "Output File Name as Field" setting of the input?  That, coupled with, perhaps a tile tool and filter might help you accomplish what you set out to do.  


Can you share sample data and/or what field you're using to select records?

 

File Name as Path.png

Asteroid

Hey @john_miller9 ,

 

 

Attached is some sample data and my current workflow. They are pulling from the same file but with different tabs but the data each month the data comes from a new file where I need to be able to put all the months in one single folder location and run the all together and append the new month with the current list from the rest of the months. Thanks! 

Alteryx Partner

@chughes49 ,

 

Given that your files/sheets are formatted the way they are, I believe you are correct in that a macro is probably the best path forward.  See the files attached which I think accomplish what you're looking to do.

 

First, I created a batch macro and incorporated it into this workflow to import all files/sheets in a location.  Each file/sheet is run through the macro and the output of each run is appended to the previous.

 

Multiple Files_Sheets2.png

 

Note that for the file input, I'm using a wildcard to pull in all files that start with "Sample" and are in an excel format (.xlsx).  Next, notice that we're only bringing in the list of sheet names along with the full path of the file, rather than the data itself.

 

Multiple Files_Sheets3.png

Multiple Files_Sheets5.png

 

For the macro itself, I've simply taken one of your workflow paths and added an action to update the file path.  This will read from the list of files from our input of the workflow.

 

Multiple Files_Sheets6.png

 

Running this workflow will initially get all of your input files/sheets processed and into the same output file ("Final.xlsx").

 

I've also turned this into an app which will allow you to choose a file for when you want to add new file/sheets to the same output.

Multiple Files_Sheets7.png

 

Note that when you are prompted, after selecting a file you'll want to make sure you select "Import only the list of sheet names" before you continue running the app.

 

Multiple Files_Sheets1.png

 

The output here is configured to append the results to your previous "Final.xlsx" file.

 

Multiple Files_Sheets4.png

 

Hopefully this gets you to where you need to be. Let me know if you have any questions

Asteroid

@john_miller9 ,

 

 

It is not allowing me to open the file as a yxzp. Would it be possible for you to send is as a yxmd or something that I may be able to open. Thanks so much again

Cheers

 

Alteryx Partner

What version of Alteryx are you using?  The workflow and macro/app was created in 2019.2.

Asteroid

I'm running 2019.1.6 but when I hit check for updates it is saying my version is up to date

Alteryx Partner

You can download the latest release at licenses.alteryx.com.  See the blog post on this release here.

 

I've also saved as 2019.1 - let me know if this works for you.

 

Asteroid

@john_miller9 ,

 

I got the updated version finally and thanks for the help so far. I used the wildcard to create the list of sheet names but in each file there are multiple tabs  

ex Jan file

Tab 1= Stat

Tab 2= Vis

Feb file

Tab 1 = Stat

Tab 2= Vis

 

When I run it, it is taking every tab. How do I alter it to only take tab 1 from every file "stat"?

Alteryx Partner

If you're keeping the same sheet name, it should be approached a bit differently.  See the attached app, which is updated accordingly.

Labels