Update sharepoint column based on value in excel spreadsheet

Question

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:

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

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

Answer 2

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