Make short Work: Created By

Sunday, 4 December 2016

PowerShell - how to Read write csv file

Recently Some one has asked me about Reading and writing text file of csv file using powershell, its pretty simple and streght forward, but I think I should write it down that will help beginners.

Below is the code, let's have overview before jumping in to the code.

First We are loading SharePoint dll, it is require if you are performing any action related to SharePoint, if not, please remove those lines (line number 5 to 18)

Than we are checking if file exist of not,

If file exist we will laod the file using Import-CSV cmdlets

and than itarateusinf forwach loop to read all the rows.
You can read current rows specified columns value like $row."ColumnName"

#Created by Praveen to import items in SharePoitn list 
#uploaded 18728 items in minutes 4.5 minutes
#Code is free to use, but don't forget to put Author's name

# Setup the correct modules for SharePoint Manipulation
if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
{
   Add-PsSnapin Microsoft.SharePoint.PowerShell
}
$host.Runspace.ThreadOptions = "ReuseThread"

#Open SharePoint List

 $SPAppList="MyListName"
 $spWeb = Get-SPWeb "http://ServerSiteUrl/sites/ABC"
 $spData = $spWeb.Lists[$SPAppList]
 $InvFile="C:\Data_T9078.csv" 

 # Get Data from Inventory CSV File
 $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...."
 $count=1;
 foreach ($row in $tblData)
 {
    "$count) Adding entry for "+$row."ColumName1".ToString()
    $spItem = $spData.AddItem()
    $spItem["Title"] = $row."ColumName1".ToString()
    $spItem["Project"] = $row."Project".ToString()
    $spItem["Category"] = $row."Category".ToString()
    $spItem["Details"] = $row."Details".ToString()
    $spItem.Update()
    ++$count;
 }

 write-host "---------------"
 write-host "Upload Complete"

 $spWeb.Dispose()

CSV file format

You can also Export data to the CSV by refering below line of code.

$csvFile = "C:\DataExport_Pra09098.csv" 

#Array to Hold Result - PSObjects
$ListItemCollection = @()
"Starting export...."
$count=1; 

 #Get All List items where task date is before 2014
 $list.Items |  Where-Object { $_["Created"] -le "12/31/2014"} | foreach {
 $ExportItem = New-Object PSObject 
 $ExportItem | Add-Member -MemberType NoteProperty -name "Title" -value $_["Title"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Project" -value $_["Project"]
 $ExportItem | Add-Member -MemberType NoteProperty -Name "Category" -value $_["Category"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Author" -value $_["Author"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Editor" -value $_["Editor"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Created" -value $_["Created"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Modified" -value $_["Modified"]
 $ListItemCollection += $ExportItem
 write-host "$count) Getting Item ID "$_["ID"] ;++$count;
 } 
 #Export the result Array to CSV file
 $ListItemCollection | Export-CSV $csvFile -NoTypeInformation

Comments & Feedback are welcome, and yeah don't forget to share this make short link.

Praveen

Thursday, 30 July 2015

Update Modified by and Created by in SharePoint list

Some of you have face the situation to update list with large number of items or some time we have to change, so that lead to change Modified By to your name; Modified or created by names of all items, yeah we are aware about System.Update() method but if data has populated and no code action will take place and requirement says to update because we don't wants to confuse client/Business users and his customers so in very rare and after approval we execute it to update modified and created by names.

We can use below script to update thousands of list items in single shot. but take care once changes are done by PowerShell can not be roll back.

so to update SharePoint list items simply can save below PowerShell script in a .PS1 file and run it with Elevated privileges in SharePoint server where you has permission to modify items.

PowerShell:

<# Created by: MakrShortWork
   Purpose:   To update Modified and created by Names.
   Created Date: June, 2011
#>

  $web = Get-SPWeb "http://YourWebUrl"
  $spList =  $web.Lists["ListName"]  
  $ListItemID = 23;
  $UserName =$ web.EnsureUser("domain\useridLoginId");  
     
  $caml = '<Where><Eq><FieldRef Name="ID" /><Value Type="Text">{0}</Value></Eq> </Where>' -f $ListItemID
  $query = new-object Microsoft.SharePoint.SPQuery
  $query.Query=$caml
  $oItem = $spList.GetItems($query)[0]

# in List Created by -Author and Modifed by -Editor
     $oItem["Author"] = $UserName           
     $oItem["Editor"] = $UserName  

     # You can add other fields here any field value data time etc....
     $oItem.Update()
    # You can use SystemUpdate(true/false);

Please run script on SharePoint server with Admin privileges,
Share or refer the link, that may help some one

-