Importing SharePoint List Items to a CSV File via Powershell

As a follow-up to my post titled "Exporting SharePoint List Items to a CSV File via Powershell" I am posting this script that I use for importing SharePoint ListItems via Powershell.  It works in conjunction with the exporting script.

This script borrows heavily from Brian Farnhill's  excellent post Populate a SharePoint list with items in a CSV file using PowerShell .

Key additions for the way I use it are the exclude columns array and the iterating of all lists in a web then attempting to load the list items for that list.  It accepts 2 parameters, the url of the web to import to and the path to the folder with the csv files containing the list data.

I'm sure there is room for improvement, but it does what I want.  


Param(
 [Parameter(Mandatory=$True)]
 [string]$webUrl,
 [Parameter(Mandatory=$True)]
 [string]$csvFolderPath
)
$web = Get-SPWeb $webUrl
write-host ("Web Title: " + $web.Title)
$arrExcludeCols = "Workflow Instance ID","Folder Child Count","Approver Comments","GUID","Modified","Modified By","Is Current Version","Edit Menu Table Start",
      "Edit","ScopeId","Level","Encoded Absolute URL","Select","Item Child Count","ProgId","Order","Workflow Version","Edit Menu Table End","Item Type",
      "Has Copy Destinations","owshiddenversion","File Name","Name","Server Relative URL","Created By","Unique Id","Sort Type","Effective Permissions Mask",
      "HTML File Type","URL Path","Copy Source","File Type","Attachments","Property Bag","ID","Type","Approval Status","Version","UI Version","Client Id",
      "Path","Created","Instance ID"
     
#foreach($list in $web.Lists)
for($i=0; $i -le $web.Lists.Count; $i++)
{
    $list = $web.Lists[$i]
    $csvPath = $csvFolderPath + '\' + $list.Title + '.csv'
    write-host ("Importing: " + $csvPath)
    try {
      $csvRow = $null
      Import-Csv $csvPath | ForEach-Object {
        $csvRow = $_
        $newItem = $list.Items.Add()
        Get-Member -InputObject $csvRow -MemberType NoteProperty | ForEach-Object {
        $property = $_.Name
        if ($arrExcludeCols -notcontains $property)
        {
        #write-host -foregroundcolor yellow ($property + ' : ' + $csvRow.$property)
        try {
            $newItem.set_Item($property, $csvRow.$property)
        }
        catch  [Exception]
        {
            write-host -foregroundcolor red ($_.Exception.Message)
           
        }
        }
        $newItem.Update()
        }
      }
    }
    catch  [Exception]
    {
        write-host -foregroundcolor red ($_.Exception.Message)
        #write-host -foregroundcolor red ("Could not import list items.")
    }
 

}

Exporting SharePoint List Items to a CSV File via Powershell

What?  A powershell script to export all lists from a web to csv files.

Why?  Doesn't SharePoint offer multiple way to move data?  Yes.  And they never quite seem to do what I want.  If I want to rebuild a development web after making schema changes to a list and I try to use any of the native SharePoint tools something always seems to go wrong.  If you want to share development data with other developers using the native tools, again good luck.  If you have multiple demonstrations and wish to install them on different farms and perhaps to different levels of the SharePoint product good luck.  Sometimes these things work, others times they just bomb.

Using a .csv file to achieve the same thing means you have just the data without any of the baggage that often comes with using SharePoint.

This script accepts 2 parameters, the web url and the output path for the csv files (you must manually create the output path if it does not exist).  It then get all lists and exports them to csv files where the names are the list title + .csv (yourListTitle.csv).

Here is the script:



Param(
 [Parameter(Mandatory=$True)]
 [string]$webUrl,
 [Parameter(Mandatory=$True)]
 [string]$outPath
)
$web = Get-SPWeb $webUrl
write-host ("Path: " + $outPath)
foreach($list in $web.Lists)
{
  $exportlist = $null
  $exportlist = @()
  $list.Items | foreach {
    $hash = $null
    $hash = @{}
    foreach($fld in $_.Fields ){
      Try {
        $hash.add($fld.Title, $_[$fld.Title])
      }
      Catch [System.Management.Automation.MethodInvocationException]
      {
        # Eating an error caused by duplicate column names.
      }
      Finally
      {
        #"End"
      }
    }
    write-host ("Exported: " + $_.Title)
    $obj = New-Object PSObject -Property $hash #@{
    $exportlist += $obj

  }
  $expath = $outPath + '\' + $list.Title + '.csv'
  $exportlist | Export-Csv -path $expath #$oPath

}