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.
@JerseyJack I have made several additions to my work around since I came up with my original approach to copying files out of SharePoint and it is a fairly polished procedure at this point at my company.
Alteryx cannot read and write files directly from SharePoint because it has trouble authenticating.... however, Alteryx can trigger events that can move files out of SharePoint into a location your local computer and the Alteryx server can access.
To do this, you need to use events in your workflow to run tools that are built into Windows. The tools used are called Robocopy (short for robust copy) and net (short for network).
Here is a step by step guide: (Please forgive the redactions... I need to keep my companies file structure secret for security purposes)
1. Identify the SharePoint directory of the file(s) you need to get and copy the URL into notepad
2. Change the url to a format that will work in windows explorer
3. Find a network location that both your local machine and you Alteryx server can access and create a folder there. I usually include SharePoint replication in the folder name.
4. Next... we need to use the network tool to create a temporary mappings to both the SharePoint network address location and the replication network address location. We will do that through Alteryx using the Net command.
4A. Go into Alteryx and click on an open spot in your canvas and select the "Events" tab on the left pane.
4B. Click the "Add" dropdown and select "Run Command..."
4C. In the "Run Event When" drop down, select "Before Run"
4D. In the "Command:" textbox enter "net"
4E. In the "Command Arguments [Optional]" textbox enter: "use v: "\\sharepoint.[your company site].com\[SharePoint network address location]" /persistent:no"
What this will do is temporarily map a network drive to your sharepoint directory (called v:... change it to another letter if v: is in use already). I have found that this step is necessary because it touches the network location... without it, you will sometimes get a network path not found error.
Before we go further though, I usually put in another event to remove the mapping we just created after the workflow is completed.
4F. Repeat step 4A and 4B
4G. In the "Run Event When" drop down, select "After Run"
4H. In the "Command:" textbox enter "net"
4I. In the "Command Arguments [Optional]" textbox enter: "use v: /delete"
5: Repeat all of the steps in step 4 to map a second drive to the replication network address location (I usually use w: for the drive name).
6. Next go to the replication network address location and create a text file in notepad and name it "Robocopy_Batch.bat" (you can call it whatever you want, but this is how I will be referencing it below). Be sure to click the "Save as type:" dropdown and select "All Files"
7. After you have created your batch file, you will need to right click on it and click edit it from this point onward to make any changes. On the first line of the file enter:
This will take a mirror copy of the specified file out of the SharePoint drive (mapped to v:) and save it to the SharePoint replication network address (mapped to w:). It will also create a log file called SyncLog.txt and place it in the SharePoint replication network address
8. On the 2nd line enter: "EXIT /B"
This will force the batch file to exit without errors. If you do the robocopy command directly in Alteryx (as specified in my earlier post)... it will work, but you will get an error... even when it runs successfully... this prevents that error.
9. Save and close the batch file and go back into Alteryx and navigate back to the events tab.
10. Click on the "Add" dropdown and select "Run Command..."
11. In the "Run Event When" drop down, select "Before Run"
12. In the "Command:" text box type: W:\Robocopy_Batch.bat and click ok
12. Reorder your events to make sure the before run net events go before the robocopy event and the after run net events run after the robocopy event.
13. Run the workflow to replicate the files to the replication SharePoint network location.
14. Use the file that is inserted into the replication SharePoint network location for your workflow.
You can repeat this entire process in reverse to write files manipulated in Alteryx back to SharePoint if you need to.
This process took a lot of trial and error, but I have been finding myself using it often because there are a lot of cases where tracking and project management files are kept on SharePoint and this allows access to them.
Many thanks for taking the time to share this detailed & excellent guide. I was aware that a cludgy Robocopy (versed in Robocopy) and re-map based workaround was possible but it's good to hear that someone is using it regularly in the wild and it's tried and tested.
It has some downsides of course. As this isn't checking out the file from Sharepoint there is a window of time, while the file is being used outside of Sharepoint and read/updated, during which a race condition exists. If used for automating critical processes there is a risk there. However, some of that could be mitigated through training etc but not completely removed.
My suspicion is that other non-Sharepoint (I'm trying to avoid using Nintex as building on-top of an already complex platform like Sharepoint seems like asking for trouble) workflow/bpm/ETL are all going to face this issue but I'm still appraising them.
You are correct. If someone makes a change to the version in SharePoint during the timeframe that the workflow is actually running, there is a risk that the change saved on SharePoint will be overwritten by the copy inserted by Robocopy.
I mitigate that risk my scheduling my workflows to run outside of work hours. The odds of someone updating the spreadsheet on Sunday between 2:00 AM and 2:02 AM is pretty remote. Informing the end users that the possibility exists is another mitigating factor.
I appreciate your pointing that out. I forgot to mention it in the walkthrough.
I can get this to work when I run my workflows locally but when I try to run them using the server it kicks back with an error. Has anyone been successful using UNC file paths to publish to SharePoint using the server?
I have unsuccessfully tried to use a UNC path on Alteryx Server. I believe this is because the UNC path in Developer is using your desktop, which is not the same as the server's "desktop" unless the server is added to your SharePoint site. Still working on a good solution.