1

I have what would seem a simple task below but I’m afraid I'm new to Powershell and I've yet to find a solution that works.

Scenario

I have a Document Library called "Archive" that will have many documents in it. All documents in this document library will have the same ContentType and hence the same columns.

The two relevant columns are: 1.WhereLegacyID (Which will not be null) 2.SetPersonID (Which will be null)

For every document within the document library my requirement is to:

‘WhereLegacyID’ is a certain value, Set SetPersonID to another value.

Those values need to be determined by reading in a spreadsheet (below) which will have 2 columns:

  1. Column A = WhereLegacyID and
  2. Column B = SetPersonID.

The number of rows should match the number of documents in the document library:

Eg showing only 2 documents

So the script would need to find any documents WhereLegacyID = 1 and SetPersonID = 11 etc

One last point, I expect that there will be multiple documents with the same WhereLegacyID and so the script would need to loop through all the documents and then set the SetPersonID accordingly.

Would anyone know how this is done?

Also, can anyone recommend any good Powershell v2 for SharePoint 2010 training resources online?

Many Thanks Jason

2
  • For training you can check start by going to mva, you can find plenty of ressources and courses there – Red Jun 9 '16 at 16:00
  • Hi Red, thanks for replying. Unfortunately I can only find 1 PS course on their and its not really relevant (Using PowerShell to Manage Office 365 Users) Regards Jason – Jason Jun 10 '16 at 8:18
1

Ok I've cracked it...

Remove the check-out and check-in bits if you haven't set this as a condition on your document library.

### DECLARATIONS ###
$SPWeb = <enter as appropriate>
$SPList = $SPWeb.Lists["<enter as appropriate>"]
$PublishText = "<enter as appropriate>"
$SPItems = $SPList.Items
$Doc_Check_Count = 0
$Doc_Updated_Count = 0

# Load in the CSV
$CSV_List = Import-Csv <enter path of csv as appropriate>

## Document Library Loop ##
foreach($SPItem in $SPItems)
{

            ## CSV LOOP 
            FOREACH($Line in $CSV_List)
            {

                # Declare and assign CSV variables and values
                $CSV_Value_To_Match_Item = $Line.<enter appropriate column_Header_in_csv>
                $CSV_Value_To_Be_Passed_Back_To_Item = $Line.<enter appropriate column_Header_in_csv>

                if ($CSV_Value_To_Match_Item -eq $SPItem["<Appropriate Column Value of document/item>"])
                {    

                    #Check the document out
                    $url = [String]::Format("{0}/{1}",$SPWeb.Url, $SPItem.File.Url)
                    $file = $SPWeb.GetFile($url)
                    $file.CheckOut()

                    #Update the metadate for the document
                    $SPItem["<Appropriate Column Value of document/item>"] = $CSV_Value_To_Be_Passed_Back_To_Item 
                    $SPItem.Update()

                    #Check the document in
                    $file.CheckIn($PublishText)
                    $Doc_Updated_Count = $Doc_Updated_Count + 1
                }
                #Else
                #{
                #    write-host "Does NOT Match"
                #}
             }
    $Doc_Check_Count = $Doc_Check_Count + 1
}                
write-host $Doc_Check_Count "documents checked"
write-host $Doc_Updated_Count "documents updated"    
0

Excel can't write back to SharePoint, but MS-Access can.

Your requirement can be done with one SQL statement:

UPDATE "Archive" SET PersonID= ... WHERE LegacyID= ... 
2
  • Hi Danny, thank for taking the time to answer.. are you sure about excel? I've found the following on this site: sharepoint.stackexchange.com/questions/151486/… which suggests it can be done. On saying that I'm currently getting errors but that's more likely to be down to my newbie powershelling! – Jason Jun 10 '16 at 8:15
  • That is Powershell doing the middleman job, Excel by itself can not write to SharePoint. Access can – Danny '365CSI' Engelman Jun 10 '16 at 9:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.