Import Excel/CSV from SharePoint with Metadata Filter

[Kaycee]

Hi all, 

I have documents stored in SharePoint but rather than using folders, I use metadata (columns) to tag files.  The SharePoint Folders Import doesn't include metadata columns but do include file content.  The SharePoint Lists, Odata etc don't appear to include file content but do have metadata.

Is there an option where you can retrieve the metadata (aka. custom columns created in SharePoint), filter using those columns and then extract the content from the files?

Thanks
Kaycee

[Kaycee · Accepted solution]

I was fortunate enough to be given this advice in another forum which is effective & efficient:

Step 1
Create a helper query MetaFileNameList that generates a list of file names based on the metadata

Step 2
Use that list to filter your query with SharePoint.Files or SharePoint.Contents, something like:

Table.SelectRows( PrevStepName, each ( List.Contains( MetaFileNameList, [FileNameCol] ) =true ))

[MFelix]

Hi @Kaycee 

For this you need to connect to ODATA for you Sharepoint site should be in:

https://<yoursharepointsite>/_vti_bin/listdata.svc

Then you just need to select the documents folder:

The Test Column is one that I created on my sharepoint list.

[Kaycee]

Thanks @MFelix for taking the time to reply. 

I can get to this point ok and navigate to the files - but then once I have used the metadata to filter to the files I need, how do I then import the data (contents) of that file? 

Thanks!

[MFelix]

Hi @Kaycee,

Then you need to expand the binary colum  that open the files

[Kaycee]

Unfortunately [Binary] isn't an option.

The only 'expandable' columns that come up are [CreatedBy], [ModifiedBy] and [CheckedOutTo]. There are a heap of other columns but all just contain straight content (such as [Version], [Path], [ApprovedStatus], [ContentTypeID].

[MFelix]

Hi @Kaycee ,

You are correct sorry, in this case you can do a merge between the get data from OData,Feed and the Sharepoint list.

This link has to be done using the file name (that I assume are different) be sure to select only the  lines that are present on the ODataFeed. then you can Expand the Binary:

Has you can see the test column is here and the binary also. If you need further assistance like a step by step please tell me.