How to Input Latest File From SharePoint

[drjeannette]

Hello,

I am trying to Input the data from the latest Excel file from a SharePoint folder.  New files are loaded to this folder daily and are the input to an Alteryx workflow. Prior to SharePoint, we were using a network drive and I was able to use the Dynamic Input tool in combination with some formulas to identify the latest file to open.  However, I cannot seem to figure out how to replicate when the the Input file is coming from SharePoint instead of a Directory.  Any simple solutions?  Thanks for the help for helping out a no!

[snigdha_karre]

Hi - Did you find solution for your workflow?

[drjeannette]

Not yet.  I was hoping someone could help me in the community.  Until then, I am still using network drives. 

[cjaneczko]

Are you able to sync you sharepoint folder to your One Drive? If you are, once its synced it acts just like a network folder and appears in your File Explorer. You should be able to use the Dynamic or Input tool as normal. At my prior employer this is what I had done. You can also take a look at the Sharepoint Input/Output tools in the link below. If you can sync to One Drive though, you shouldnt need the SharePoint tool.

SharePoint Input Tool | Alteryx Help

[snigdha_karre]

Thankyou. I have synced up to my network drive, but wanted to check if there is any other solution to pick up the latest file from SharePoint location.

[CullenJ]

I would also like a way to do this, the OneDrive approach isn't an option because we run workflows on alteryx server so all the input and output goes through sharepoint tools

[Eduardo_Pavan1]

Batch Macro works. Configure first Sharepoint input to read only list of full paths and add created to the metadata options on configure file. Select only the file you want with a filter and order to get the last created. Open up a new workflow to create a batch macro, configure the sharepoint input to read any of the files on the folder and use a control parameter, use the action to update value itermName - Value Field of the sharepoint input tool. Just add the macro to the first workflow and configure it to use the Name field as control parameter. Can't share mine, it's full of credentials, but you can figure it out with a batch macro lesson.

[SumitBansal]

Can you please share the image atleast?

[CullenJ]

Ok, I got this working following what @Eduardo_Pavan1 said. Here's some screenshots (cc @SumitBansal )

Here is the flow where I use the batch macro (the blue circle tool shown here):

 

In the Sharepoint Config 'Select File' tab, go to the directory you are reading from and set it to "Read Only List of Full Paths". On the "Configure File" tab, select "Modified" in the Attach Metadata section.

The sort tool sorts descending on Modified. The Sample tool takes the first 1 row, so the row describing the most recently modified file in the directory. In the macro config (on this workflow, not the macro workflow), choose the "Name" field as input. I had to run the flow once and have it error before it showed the column names to choose from in this config.

This is the macro workflow:

The Sharepoint tool is set up to read a random file from the directory we're targetting, in my case it's called output.csv. 

The action tool should be set to overwrite the filename part of the Path:

 For the control parameter, go in the interface designer -> properties, and set 'Output fields can change based on macro's configuration or data input'

 

Running the flow, it works for me.

This was just a quick thing, I'm sure it can be improved a lot. For example the way I have done it, it only works for that one directory which is basically hardcoded into the macro - I'm sure you could tweak the action tool and what you pass in to be more flexible. Also I haven't done any filtering to make sure I only pick up csv files, so if there is a newer non-csv file in that directory it could mess it up. But hopefully this will help some people get started with this approach.