How to export Powershell script information to Sharepoint?

2013-12-23

Question

_{Monday, December 23, 2013 8:32 PM}

I'm trying to export information gather from a Powershell script to a Sharepoint list. I've got a couple of powershell scripts that gather general server information from a server ex: server uptime, disk space, service tag, etc. and it will export the information to a csv file. What I would like to do is out-put the same information gathered by the powershell scripts to a Sharepoint list directly if at all possible.

Ex:

# all this does is reads from a list and runs a script call "boottime.ps1"

get-content "\\%Data-Path-Of-List%\computers.txt" | %Data-Path-Of-Script%\boottime.ps1 |  Export-csv %Data-Path-For-CSV\Computers.csv

# then just exports the information from the boottime.ps1 script to a csv file
#I also have a script that will upload the information to a sharepoint list.
# I found that I have to run this in version 2 of powershell, so I just open a DOS prompt in Admin Priv's and type the following

powershell.exe -version 2.0

# Next I make sure the Sharepoint snap-in is loaded

if ( (Get-PSSnapin -Name Microsoft.sharepoint.powershell - erroraction silentlycontinue) -eq $null)
{
   Add-PsSnapin Microsoft.Sharepoint.Powershell
}

$spweb = get-SPweb $spserver
$spdata =$spweb.getlist("%URL_Of_My_List%")

# this is the same location from the orginal Powershell script previously stated.

$ComputerInfoFile = "%Data-Path-For-CSV%\Computers.csv"
foreach ($rows in $tblData) {

# here is where I add the information from my csv file
# 2 things needs to be present

#   1st the colums have to be present in the sharepoint site before I can upload the information
#   2nd the columns have to the headers in my csv file

$spItem = $spData.AddItem()
$SpItem["ServerName"] = $row."ServerName".toString()
$SpItem["Uptime"] = $row."Uptime".toString()
$SpItem.Update()
}

# this just disconnects from Sharepoint

$spWeb.Dispose()

Please dismiss all the comments it just helps me understand what the code is doing, also if this is not the correct place to post this question I appologize in adavance and ask that if this is the incorrect place to post this question please provide me a link to a where I can post such questions.

All replies (7)

_{Monday, December 23, 2013 9:03 PM ✅Answered}

looks like you are pretty close.

I'd replace:

$ComputerInfoFile = "%Data-Path-For-CSV%\Computers.csv"
foreach ($rows in $tblData) {

with

$ComputerInfoFile = Import-Csv ‘C:\MyData.csv‘

foreach ($rows in $ComputerInfoFile ) {

See if that works. Here is an example I use when I need to add/update items in a list from a csv

#get data
$csv = Import-Csv ‘C:\MyData.csv‘
#get list
$web = Get-SPWeb -identity “MyTargetListURL”

$SPList = $web.Lists["MyListName"]

foreach ($row in $csv) {
#Check for existing item
$SPItem = $SPList.Items | Where { $_["ItemId"] -eq $row.ItemId }

    If ($SPItem -ne $null) {
        $SPItem["ColumnNumberType"] = [System.Convert]::ToInt32($row.ColumnNumberType)
        $SPItem["Column with special-chars"] = $row."Column with special-chars"
        #rest of fields

        $SPItem.Update()
    }
    Else {
        $SPNewItem = $SPList.Items.Add()
        $SPNewItem["ItemId"] = $row.ItemId
        #rest of fields

$SPNewItem.Update()
}
}

Alex

_{Monday, December 23, 2013 9:32 PM ✅Answered}

You would need to find the item (http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splist.getitems.aspx), e.g. via Title, then you could update that item using $spItem.Update().

Trevor Seward

Follow or contact me at...

This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

_{Friday, January 10, 2014 1:27 AM ✅Answered}

Sorry for the delay in posting this, but I ended up getting working. I'll post it in the hopes that my head scratching will save someone else some head scratching:

I ended up writting 3 PS scripts and one batch job.

1st Batch file

powershell.exe -version 2.0 -command \%Script-Location\Get-Server-Infor-4-SP.ps1

powershell.exe -version 2.0 -command \%Script-Location\Delete-list-Items.ps1"

powershell -veriosn 2.0 -command \%Script-Location\Populate-SP.ps1

1st PS script that gets the info:

get-content \%Location-Of-My-File-With-List-Of-Servers%\name-of-file%.txt | \%Location-Of-My-Script-To-get-the-Information-I-want | Export-csv \%location-of-my-output\filename%.csv

Ex: get-content C:\scripts\computers.txt | C:\scripts\boottime.ps1 | export-csv C:\scripts\computer.csv

2nd PS script Delete-List-Items.ps1

# http:

#Script 1 Boottime.ps1:# This script permits to get UpTime from localHost or a set of remote Computer# usage# localHost# .\BootTime.ps1# set of remote computers# get-content .\MyserverList.txt | .\boottime.ps1# Optionally pipe output to Export-Csv, ConverTo-Html Process {   $ServerName = $_    if ($serverName -eq $Null) {        $serverName= $env:COMPUTERNAME  }   $timeVal = (Get-WmiObject -ComputerName $ServerName -Query "SELECT LastBootUpTime FROM Win32_OperatingSystem").LastBootUpTime #$timeVal   $DbPoint = [char]58 $Years = $timeVal.substring(0,4)    $Months = $timeVal.substring(4,2)   $Days = $timeVal.substring(6,2) $Hours = $timeVal.substring(8,2)    $Mins = $timeVal.substring(10,2)    $Secondes = $timeVal.substring(12,2)    $dayDiff = New-TimeSpan  $(Get-Date –month $Months -day $Days -year $Years -hour $Hours -minute $Mins -Second $Secondes) $(Get-Date)        $Info = "" | select ServerName, Uptime    $Info.servername = $servername  $d =$dayDiff.days   $h =$dayDiff.hours  $m =$dayDiff.Minutes    $s = $daydiff.Seconds   $info.Uptime = "$d Days $h Hours $m Min $s Sec"       $Info }#Script 2: Delete-List-Items.ps1# http://markimarta.com/sharepoint/delete-all-items-in-sharepoint-list-using-powershell/# there seems to be a problem with running this script in version 3 or later, the workaround is to run it in version 2# below is the cmd for doing so, just open up a DOS prompt with Admin Privileges Start-->Run-->cmd# type then copy and paste the following line the DOS window then you can run this script#powershell.exe -version 2.0# make sure that the Microsoft.SharePoint.PowerShell Snap-in is installed as well if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null ) {    Add-PsSnapin Microsoft.SharePoint.PowerShell } # "Enter the site URL instead http://serverurl" $SITEURL = "%http://serverurl%" $site = new-object Microsoft.SharePoint.SPSite ( $SITEURL ) $web = $site.OpenWeb() "Web is : " + $web.Title # Enter name of the List below in the [“%List-Name%”]$oList = $web.Lists["%List-Name%"]; # This echo out the name of the list its going to be deleting the records from "List is :" + $oList.Title + " with item count " + $oList.ItemCount # It’s just counting the rows/records $collListItems = $oList.Items; $count = $collListItems.Count - 1 # Here is where it is actually deleting the records and then out put the number or the record it deletedfor($intIndex = $count; $intIndex -gt -1; $intIndex--) {             "Deleting record: " + $intIndex             $collListItems.Delete($intIndex); }  #Script 3: Populate-SP_Test.ps1# http://blogs.technet.com/b/stuffstevesays/archive/2013/07/10/3577320.aspx# there seems to be a problem with running this script in version 3 or later, the workaround is to run it in veriosn 2# below is the cmd for doing so, just open up a DOS prompt with Admin Privileges Start-->Run-->cmd# type then copy and paste the following line the the DOS window then you can run this script#powershell.exe -version 2.0# make sure that the Microsoft.SharePoint.PowerShell Snap-in is installed if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null ) {    Add-PsSnapin Microsoft.SharePoint.PowerShell } ############################## Here are some more varables that can be added I was not able to get this to work #$SPComputerInfo="/Lists/PowershellTest/" ############################## Here is where we connect and Open SharePoint List via Powershell$SPServer= "%http://serverurl%$spWeb = Get-SPWeb $SPServer$spData = $spWeb.GetList("%List-Name%")# This is the variable for the path that has the file I want to input to SharePoint List$InvFile="\\%location-ofList%\computers.csv" # This is just some error checking to make sure the file exist $FileExists = (Test-Path $InvFile -PathType Leaf) if ($FileExists) {    "Loading $InvFile for processing..."    $tblData = import-csv $InvFile } else {    "$InvFile not found - stopping import!"    exit }# Loop through Applications add each one to SharePoint "Uploading data to SharePoint...." foreach ($row in $tblData) {    #Here is where I add the information from my CSV file   #2 things have to be present     #      1. the columns have to be in the sharepoint site before I can import the information   #      2. columns have to the headers in my csv file    #"Adding entry for "+$row."Computer Information".ToString()    $spItem = $spData.AddItem()   $spItem["ServerName"] = $row."ServerName".ToString()    $spItem["Uptime"] = $row."Uptime".ToString()    #$spItem["DNSHostName"] = $row."DNSHostName".ToString()    #$spItem["DistinguishedName"] = $row."DistinguishedName".ToString()    $spItem.Update() }# This is just disconnecting from SharePoint$spWeb.Dispose()

Enjoy, and if anyone has a better way of doing this I'm interested in knowing, thanks again

Thanks in Adavance

_{Monday, December 23, 2013 8:37 PM}

Take a look at http://blog.brianfarnhill.com/2012/07/populate-a-sharepoint-list-with-items-in-a-csv-file-using-powershell, which is exactly what you want to do using a PowerShell function.

Trevor Seward

Follow or contact me at...

This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

_{Monday, December 23, 2013 9:03 PM}

Trevor,

Thanks for the link. Unless I'm wrong (which is entirely possible) this link is just a different style of how to upload the information from a csv file? I think I've got the idea of how to actually upload the information in the csv file to the Sharepoint site. What I'm trying to do is instead of having one script to create a csv file and another one to upload the information to a sharepoint list is to just go directly to sharepoint list from the Powershell script that is gathering the information. The sudo code in my head looks something like this:

get-content %Data-Path-For-List%\Computers.txt | %Data-Path-For-Script%\boottime.ps1 | SPweb-export -list %URL-Of-My-SPList%

_{Monday, December 23, 2013 9:06 PM}

Trevor,

Thanks for the link. Unless I'm wrong (which is entirely possible) this link is just a different style of how to upload the information from a csv file? I think I've got the idea of how to actually upload the information in the csv file to the Sharepoint site. What I'm trying to do is instead of having one script to create a csv file and another one to upload the information to a sharepoint list is to just go directly to sharepoint list from the Powershell script that is gathering the information. The sudo code in my head looks something like this:

get-content %Data-Path-For-List%\Computers.txt | %Data-Path-For-Script%\boottime.ps1 | SPweb-export -list %URL-Of-My-SPList%

I would generate a CSV, even if it was temporary (run boottime.ps1, create csv, import into SharePoint, delete csv). It'll be easier :-)

Trevor Seward

Follow or contact me at...

This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

_{Monday, December 23, 2013 9:25 PM}

Trevor and Alex,

Thanks for post it I truely appreciate the suggestions. I'll admit I was hoping to find complete it in one step, but if doing it in two steps works I can live with it.

One thing that I'm not postive on is when I want to update the information, example I rebooted one of the servers and so the "uptime" information is different from the last time I ran the gather information scripts. When I go to upload the new information I keep just getting more and more rows in my list. I think its has to do with either the

$SpItem = $spData.AddItem() #(I think its ?Line 32?)

$spItem.Update #(I think its ?Line 35?)

I like to just replace the row all together do either of you know the syntax or where I could fine it? I thinking is something like

$SpItem = $spData.ReplaceItem()

$spItem.Replace

Again thanks again in adavance for your help

Share via

How to export Powershell script information to Sharepoint?

Question

All replies (7)

Additional resources