Uploading files to SharePoint Library and referencing excel file to fill in column values

Question

I am brand spanking new to SharePoint. I have been tasked with developing a SharePoint site that will facilitate finding documents, and better organize them than they currently are on our network drives. I am now trying to set up a specifications library.

there are:

• Specifications
• Standard Specifications Exceptions
• Non Standard Specification Exceptions

The non standard specification exceptions are also broken into 4 regions where the same exemption file name can have different contents.

Originally I was planning on uploading everything into one Library and using different views to see the information. But after reading another question on here, it sounds like a separate library for each is the better approach.

In the end I will have probably about 3K +/- documents to upload. They will be a mix of PDF, XML, DOC, and DWG. Every file will have a unique name. Some of the file formats will be:

NSP BIRD.XML
NSP 001A820.XML
NSP 100F08M.xml
OPSS 0202.pdf
OPSS 0304.pdf
SSP 101F23.xml
SSP 102S05.xml
MTOD 0101.0700.dwg
MTOD 0202.0400.dwg
OPSD 0100.0100.dwg
OPSD 0100.0110.dwg
SSD 0002.0001.dwg
SSD 0005.0002.dwg

Currently each of the type defined by the first 3-4 characters is in its own directory. Also each directory has an excel file which holds an index. Which are all similar, but column names, count and position may vary slightly from one index to the next.

An example index would look like:

Based on this table I know that SSD 0002.0001.dwg has the title:

Structural Standard Drawings Layering Structure

And that its Status is:

Active

While I can drag an drop 100 files at a time and manually go into the library an and fill in the column information for each file it is rather time consuming.

Is there a way for the files to be uploaded and have the appropriate column information pulled from the index file?

As a followup question is there a better way of doing this? Previously I just made a hyperlink formula in each index that linked to the file on the network drive.

No clue what to tag this as either so some assistance with that would be appreciated.

Answer 1

In order to automate the population of metadata, you are going to have to interact with SharePoint programmatically. There are a few ways to do this:

• Create a console app or some other .NET/C# program and use the .NET CSOM libraries to interact with SharePoint.
• Write some JavaScript code to interact with SharePoint using the JSOM libraries.
• Personally, I'm not a big fan of the JSOM way of doing things, so if I were going to use JavaScript, I'd be writing it to interact with the REST API.
• Or, you could do it with a Powershell script.
• Come to think of it, you could probably use the REST API from a C# console app also (but if I were going down the C# path, I'd probably stick with the CSOM).

So, choose your poison, and happy coding!

Answer 2

Since this is SharePoint online, this is super easy using Microsoft flow (hopefully you are familiar with Flow or can easily get up and running with the below steps). All the documents have unique names. I would upload the documents to different libraries (perhaps using the free SharePoint migration tool), Then format the excel file(s) containing the indices as tables and upload it to a SharePoint document library or OneDrive folder Then create a Microsoft flow selecting button as the thrigger. The steps will be as follows:

1) get files (properties only) from the document library, 2) for each, get row from the excel table in OneDrive or SharePoint (add a filter to retrieve using the document name) 3) update the file property gotten from step 1.

I hope to give a more detailed answer when I get to my computer. Have a look at the below link for some more information: https://www.chakkaradeep.com/2018/05/01/deep-dive-into-get-items-and-get-files-sharepoint-actions-in-microsoft-flow/