Forum Discussion

Kaycee's avatar
Kaycee
Advocate I
4 years ago
Solved

Import Excel/CSV from SharePoint with Metadata Filter

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...
Need Help
  • Kaycee's avatar
    Kaycee
    4 years ago

    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's avatar
MFelix
Super User
4 years ago

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's avatar
Kaycee
Advocate I
4 years ago

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's avatar
    MFelix
    Super User
    4 years ago

    Hi Kaycee,

     

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

    • Kaycee's avatar
      Kaycee
      Advocate I
      4 years ago

      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's avatar
        MFelix
        Super User
        4 years ago

        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.