Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

Download Files from Sharepoint 2013

Highlighted
6 - Meteoroid

Hi, I have a SharePoint site where we have a lot of Excel-files I like to read. The excel-files also include properties in added customized columns in SharePoint, that consist of important information. I'm using Alteryx Designer x64 v2018.2.6.51223.

 

With the tool SharePoint Tool Input, I got the following columns regarding the files:

 

Field          Type      Size

Modified       DateTime    19
Modified By    V_WString  255
Company Code   V_WString   10 (added customized column in SharePoint)
Created By     V_WString  255
Created        DateTime    19
Version        V_WString  255
Content Type   V_WString  255

 

In addition, I tried to extract the files using the Directory-tool, both with an UNC-path and to a synchronized folder on my C-drive. In both cases I got all the files with the given columns:

 

Field                 Type       Size

FullPath              V_WString 32767
Directory             V_WString 32767
FileName              V_WString   260
ShortFileName         V_WString    14
CreationTime          DateTime     19
LastAccessTime        DateTime     19
LastWriteTime         DateTime     19
Size                  Int64         8
AttributeArchive      Bool          1
AttributeCompressed   Bool          1
AttributeEncrypted    Bool          1
AttributeHidden       Bool          1
AttributeNormal       Bool          1
AttributeOffline      Bool          1
AttributeReadonly     Bool          1
AttributeReparsePoint Bool          1
AttributeSparseFile   Bool          1
AttributeSystem       Bool          1
AttributeTemporary    Bool          1

 

I also tried to join those two with the help of creation and modified date and time, but no match that way, due to different time zones where the files are created, and SharePoint server-time.

 

How can I get the full path of all files in SharePoint, with the properties in the customized columns? 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
The directory input tool only gives you information about files within a specific folder, in order to read a file into Alteryx off the back of this you need to use the 'dynamic input', have a look at this and let us know if you have any follow up questions.

Highlighted
6 - Meteoroid

I have tried out the dynamic input, but are not able to see that this tool give me any file-properties, only the content in the file. I need to get out the customized file properties that are assigned to the files, and also created as columns in SharePoint. Is that possible? 

 

PS: I saw there was an Idea "Enhance SharePoint List Input to Support Content Types" about a similar case. 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Okay I'm not sure I can answer then, let's see if someone else jumps in...

 

Ben

Highlighted
6 - Meteoroid

There is a possibility to create a query in SharePoint 365 of the type Microsoft Excel Web Query (*.iqy):image.png

 The query has the following string: 

 

 

WEB
1
https://company.sharepoint.com/sites/sitename/_vti_bin/owssvr.dll?XMLDATA=1

&List={1234ABCD-A1B2-3C45-6789-0A1234567890}

&View=A1B2C345-D6E7-89F0-123A-B4C567D89012

&RowLimit=0

&RootFolder=undefined

   Selection={1234ABCD-A1B2-3C45-6789-0A1234567890}-A1B2C345-D6E7-89F0-123A-B4C567D89012
   EditWebPage=
   Formatting=None
   PreFormattedTextToColumns=True
   ConsecutiveDelimitersAsOne=True
   SingleBlockTextImport=False
   DisableDateRecognition=False
   DisableRedirections=False
   SharePointApplication=https://company.sharepoint.com/sites/sitename/_vti_bin
   SharePointListView=A1B2C345-D6E7-89F0-123A-B4C567D89012
   SharePointListName={1234ABCD-A1B2-3C45-6789-0A1234567890}
   RootFolder=undefined

 

With this connection I can create an Excel-file where all wanted information is included, and I can import that file into Alteryx. Based on the filenames and path/link; I assume it is relatively easy to batch-process of the files from SharePoint in Alteryx. BUT then I need to manually update the Excel-file each time I run the process. This is not sustainable in order to schedule the task. 

 

Two questions to this:

  1. Is it possible to connect directly to the query in Alteryx? How? 
  2. Is it alternatively any way I can get Alteryx to trigger an update of the Excel-sheet, based on the query from SharePoint? How?
Labels