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"

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

$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