{"id":70,"date":"2012-09-15T14:45:28","date_gmt":"2012-09-15T19:45:28","guid":{"rendered":"https:\/\/sysadminnightmare.com\/?p=70"},"modified":"2012-09-15T14:45:28","modified_gmt":"2012-09-15T19:45:28","slug":"update-a-sharepoint-list-from-a-csv-file","status":"publish","type":"post","link":"https:\/\/sysadminnightmare.com\/index.php\/2012\/09\/15\/update-a-sharepoint-list-from-a-csv-file\/","title":{"rendered":"Update a SharePoint List from a CSV File"},"content":{"rendered":"

So in the last post <here><\/a> I created a CSV file for all the domain computers that had a Bitlocker key assigned to it.<\/p>\n

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.<\/p>\n

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.<\/p>\n

UpdateList.ps1<\/p>\n

[code]<\/p>\n

# Check if the Sharepoint Snapin is loaded already, and load if not
\nif ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
\n{
\n\u00a0Add-PSSnapin Microsoft.SharePoint.PowerShell
\n}<\/p>\n

#Setting our variables, Site name, List name, file to import and Caml
\n$spWeb = Get-SPWeb -Identity “https:\/\/<yoursite>\/Departments\/IT\/”
\n$list = $spWeb.Lists[“Bitlocker”]
\n$csv = Import-Csv Q:BitLockerComputerReport.csv
\n$caml=””
\n\u00a0
\n#sets up to remove current items from list
\n$query=new-object Microsoft.SharePoint.SPQuery
\n$query.ViewAttributes = “Scope=’Recursive'”
\n$query.Query=$caml
\n$items=$list.GetItems($query)<\/p>\n

#removes current items from list
\n$items | % { $list.GetItemById($_.Id).Delete() }
\n\u00a0
\n#adds Report to SharePoint List
\nforeach ($row in $csv)
\n{
\n\u00a0\u00a0\u00a0 $item = $list.Items.Add();
\n\u00a0\u00a0\u00a0 $item[“Name”] = $row.Name;
\n\u00a0\u00a0\u00a0 $item[“OS”] = $row.OperatingSystem;
\n\u00a0\u00a0\u00a0 $item[“Location”] = $row.Location;
\n\u00a0\u00a0\u00a0 $item[“Bitlocker”] = $row.HasBitlockerRecoveryKey;
\n\u00a0\u00a0\u00a0 $item[“Virtual”] = $row.adminDescription;
\n\u00a0\u00a0\u00a0 $item.Update();
\n}<\/p>\n

#Dispose of SPWeb, to keep things clean and no memory leaks
\n$spweb.Dispose()<\/p>\n

[\/code]<\/p>\n

Again, you need to change your site name, list name and make sure your list\u00a0has the correct columns. I would task this to run after the script that creates the\u00a0CSV file.<\/p>\n

Next I have one more post in this series where I take the\u00a0raw csv file and upload to a sharepoint library incase the bosses want to play around with the numbers (as they always do).<\/p>\n","protected":false},"excerpt":{"rendered":"

So in the last post <here> 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… Continue reading → <\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[2,7],"tags":[14,35,41],"class_list":["post-70","post","type-post","status-publish","format-standard","hentry","category-admin","category-powershell-2","tag-bitlocker","tag-powershell","tag-sharepoint"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bgeE-18","_links":{"self":[{"href":"https:\/\/sysadminnightmare.com\/index.php\/wp-json\/wp\/v2\/posts\/70","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sysadminnightmare.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sysadminnightmare.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sysadminnightmare.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sysadminnightmare.com\/index.php\/wp-json\/wp\/v2\/comments?post=70"}],"version-history":[{"count":0,"href":"https:\/\/sysadminnightmare.com\/index.php\/wp-json\/wp\/v2\/posts\/70\/revisions"}],"wp:attachment":[{"href":"https:\/\/sysadminnightmare.com\/index.php\/wp-json\/wp\/v2\/media?parent=70"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sysadminnightmare.com\/index.php\/wp-json\/wp\/v2\/categories?post=70"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sysadminnightmare.com\/index.php\/wp-json\/wp\/v2\/tags?post=70"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}