Update a SharePoint List from a CSV File

So in the last post over on http://SysAdminNightmare.com I created a CSV file for all the domain computers that had a Bitlocker key assigned to it.

Going with the work smarter not harder mantra this post will take that CSV file and update a list on our sharepoint site so I can create a graph.

Note that this must be run from a location where the Sharepoint Snap in is installed, this is usually where Sharepoint is installed so I would save this to a common drive and run from my app server.

UpdateList.ps1

[code]

# Check if the Sharepoint Snapin is loaded already, and load if not
if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
{
Add-PSSnapin Microsoft.SharePoint.PowerShell
}

#Setting our variables, Site name, List name, file to import and Caml
$spWeb = Get-SPWeb -Identity “https:///Departments/IT/”
$list = $spWeb.Lists[“Bitlocker”]
$csv = Import-Csv Q:\BitLockerComputerReport.csv
$caml=””

#sets up to remove current items from list
$query=new-object Microsoft.SharePoint.SPQuery
$query.ViewAttributes = “Scope=’Recursive'”
$query.Query=$caml
$items=$list.GetItems($query)

#removes current items from list
$items | % { $list.GetItemById($_.Id).Delete() }

#adds Report to SharePoint List
foreach ($row in $csv)
{
$item = $list.Items.Add();
$item[“Name”] = $row.Name;
$item[“OS”] = $row.OperatingSystem;
$item[“Location”] = $row.Location;
$item[“Bitlocker”] = $row.HasBitlockerRecoveryKey;
$item[“Virtual”] = $row.adminDescription;
$item.Update();
}

#Dispose of SPWeb, to keep things clean and no memory leaks
$spweb.Dispose()

[/code]

Again, you need to change your site name, list name and make sure your list has the correct columns. I would task this to run after the script that creates the CSV file.

Next I have one more post in this series where I take the raw csv file and upload to a sharepoint library incase the bosses want to play around with the numbers (as they always do).