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 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

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 ))

6 Replies

  • 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

    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 ))