This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I receive mutiple Excel files to my Outlook inbox every day that I then use within a few Alteryx workflows. I currently have these Excel files automatically moving from my Outlook inbox to a location within our company network, however this requires me to be logged in to my PC to function properly.
To get around having to keep my PC on, I've set up a Microsoft Flow to move these attachments to our Sharepoint Online site. I can connect to the files on Sharepoint when working in Designer via either the directory tool or the input tool, but when I open my workflow in Scheduler the connections are broken (Invalid Directory), and I cannot determine how to fix them.
Is Alteryx Scheduler able to read Excel files from Sharepoint? Any workarounds for this?
I've tried OneDrive and run into the same problem.
@vc at this time we only support connections to SharePoint via the SharePoint List Input and SharePoint List Output connectors included with Designer. As such connections to SharePoint Document Libraries (files and folders) are not supported. This limited support is due to authentication issues that are outside of our control when accessing files resources hosted on SharePoint. These authentication issues typically do not effect manually run workflows, but will cause workflows run from Scheduler or Gallery to fail with an authentication, file/folder not found, or file/folder not accessible error. The root cause of the issue is due to SharePoint incorrectly prompting for credentials even though credentials are provided when accessing the UNC path for the file/folder. To clarify further Scheduler/Gallery runs the workflow as part of the service in session 0 which is a non-interactive session. As such when SharePoint requests credentials in this manner the authentication automatically fails because the credential prompt can't be rendered to the screen or responded to. Based on the information you provided this is the exact issue you are encountering, and the only supported solution to this issue is to store the files you are attempting to access on a standard Windows file share instead of using SharePoint to host the files.
We resolved this issue by mapping the Sharepoint drive on the VM that the Alteryx server is on. We then use SyncToy 2.1 to “Echo” the files from the networked drive to a folder on the VM.
So as an example:
Map the Sharepoint drive on the VM to X:/sharepoint/examplefilename
once you have installed SyncToy on the Virtual Machine THEN create a “New Folder Pair” in SyncToy
Right folder: (select somewhere the Alteryx Server can access)
The Echo will duplicate the files from the Sharepoint drive to a location where the Alteryx Server can “see” it.
Create a bat file to run it(to create a bat file- open notepad and type the following code (or change it to wherever SyncToy is saved)
cd "C:\Program Files\SyncToy 2.1\"
Save this as a “.bat” file
Use Task Scheduler (on your server- it's a standard application on the server) to schedule how often you want your bat file to “echo”.
Task Scheduler setup:
Triggers: set up how often you want it to run
e.g. Daily, at 11A.M, Check the box repeat task every 5 minutes for a duration of Indefinitely. Make sure the Enable box is also checked
Actions: select “Start a Program”
Program/Script: select the location you saved the bat file
I hope that makes sense? it's all we could come up with as we use Sharepoint for everything and don't use lists at all. if anyone comes up with anything better- let me know!
so basically when building workflows- i connect to the "D:\examplefilenameecho" (the right folder) which has a duplicate of the files we need in it, and then the scheduler is able to locate them as well.
We had SyncToy installed, but it didn't play nicely with our server's Window's install.
However, using the same thought process, we discovered Robocopy (robust copy). It copies the files over to a location on the network that is accessible to both the local machine and the server, and is already built into Windows. All you need to do is set up an event to run before your workflow begins to call the Robocopy command. You don't need a task scheduler set up because it will trigger anytime the workflow is ran... and the workflow can be scheduled on the Gallery.
There is a downside I have not been able to work out yet. Every time I run a workflow with a robocopy command, it is coming back with an error although the copy actually completed successfully. I have done some googling and found that Robocopy doesn't always return a zero for a successful completion, I have not found a way to suppress these false errors in Alteryx yet.
Here are the steps I did to get this to work.
Step 1: Click on an open area on the canvas of your workflow.
Step 2: Click on the "Events" tab on the "Workflow Configuration" window.
Step 3: Check the box to "Enable Events"
Step 4: Click "Add"
Step 5: Set the "Run Event When:" drop down box to "Before Run"
Step 6: In the "Command" textbox, enter "robocopy"
Step 7: In the "Command Arguments (Optional):" textbox, put in the following command, adjusting it to use your sharepoint site and the location you want it set to. (Leave the quotes in)
Step 8: Set the timeout to a larger number it took my system about 30 seconds to run the command.
When you next run your workflow, the robocopy command should copy the directory from sharepoint to the network connection before the workflow begins. All you need to do then is have your workflow point to the copy that was just made and use it as an input.
Here is what each of the switch parameters do in Robocopy:
/MIR - (Mirror command) creates a replica of the source folder in the specified destination folder
/fft - FAT File Times (2-second granularity) - This command prevents different file systems from being classified as newer (even when the files aren't newer)
/XO - Exclude older files
/DCOPY:T - copy folder timestamps
/XA:H - Ignore hidden files
/R:1 - Sets the number of retries on failed copies
/W:1 - Wait 1 between retries
/V - Verbose output
/NP - Don't show the progress of operation
/TEE - Output to console window and log file.
/XX - Exclude extra files present in destination folder but not in source. This will prevent deletion of existing files in destination folder.
/LOG:"<file path>\<file name>" - Output status to a LOG file. Overwrites any existing logs.
We've actually figured a better way to solve this issue- by using the UNC path as your input/output file location.
to find the UNC path:
go to the "properties" section of your mapped Sharepoint drive (right click mapped drive--> properties) in the description field it actually contains the UNC path eg. \\sharepoint\DavWWWroot\Sites\yoursitename\foldername\insertyourfilenamehere
by using this as an input (or output) (it will connect to the Sharepoint site directly)
After testing it I have been able to read/write to Sharepoint - eliminating the need for Synctoy or any other workarounds- to use scheduler you must input run as credentials in the workflow for the schedule to work.