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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.

Automatically selecting an excel file from a folder

Hello,

I'm using alteryx to populate a central database of monthly performance data.

 

Each month I receive a monthly spreadsheet of data, I'm currently saving them in different folder using the file path below

 

c:data/performance of assets/2019/01/January performance data.xlsx

c:data/performance of assets/2019/02/February performance data.xlsx

c:data/performance of assets/2019/03/March performance data.xlsx

 

In this example we would not yet have the April spreadsheet so the fourth folder would be empty

 

c:data/performance of assets/2019/04/

 

I have a workflow which clears up the data, applies some statistical analysis and stores the resulting information in a central spreadsheet. This can then be used to generate performance reporting.

 

My issue is that all of this works fine if every month i manually link Alteryx to the input spreadsheets, however I can't figure a way to tell it to look in each the 12 monthly folders and pull in the data input spreadsheet only if the spreadsheet is there.

 

Does anyone have a suggestion?

 

Asteroid

With this you can use a dynamic input and then tell alteryx to check subfolders as well. If the file structure is the same and the naming convention of the new file is the same then you can wild card your input and use a single input into your workflow

 

c:data/performance of assets/*performance data.xlsx

 

In your input tool you can check the box that says Search SubDirs and it will look for the same structure throughout all subdirectories

 

That is one option or your second is to create and active director of your files

Many thanks, can you clarify what you mean by 'wild card your input' sorry, I'm new to this!

Asteroid

The asterisk that I had included in the line is the "wild Card" this basically tells the engine to pull everything from the current file and (if you checked the subdir box on the input) all subdiurectories where the file ends in _____.xls 

 

Let me know if you need more and I will  try to include and image to help explain this.

 

Better yet... I just located an article on community that explains all the options and how

 

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/ta-p/20480

Thanks, I'm so close to being there.

 

Although each of the spreadsheets I'm looking to combine is identical in format, they have multiple tabs.

 

I have a directory tool, mapping the spreadsheets which will be fed into a dynamic input (eventually), but I need to amend the directory tool to tell it which tab of the spreadsheet to take.

 

As per the guidance I use the formula tool and add the formula to the amend the fullpath: [FullPath]+"|||Sheet1$"

 

Everything seems to work, but the output from the dynamic input only includes the first spreadsheet?!

 

Very frustrating. Any idea where I'm going wrong?

 

 

Asteroid

You will need an iterative macro that will basically read through and pick up each file. If you do a search on the community board there are several people who have posted samples of what this iterative macro needs to look like and/or provided theirs for people to modify and use. 

Labels