RSS
Twitter
LinkedIn

 

SharePoint 2010 – Export SPList Data into SQL using PowerShell

Following my previous post 'Import SQL Query Data into a SPList using PowerShell' I thought I should also post on how to achieve the reverse and export data from an SPList into a SQL Table

(if only for completeness, if nothing else...)

 

First task is to make the SQL Connection and prepare the SQL Command ..


######connect to SQL database windows authentication ###########
#Set new object to connect to sql database
$connection = new-object system.data.sqlclient.sqlconnection

#ConnectionString setting for <ServerName> <databasename> with window authentication
$Connection.ConnectionString ="server=<ServerName>;database=<databasename>;trusted_connection=true"

#ConnectionString setting for <ServerName> <databasename> with SQL authentication <username><password>
#$Connection.ConnectionString ="server=<ServerName>;database=<databasename>;User Id=<username>;Password=<password>;trusted_connection=False;

Write-host "connection information:"

$connection #List connection information

Write-host "
Connecting to database.."

$connection.open() #Open Connection

#setting object to use sql commands
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.Connection = $connection

 

Then retrieve the data from SPList...

######### Get SPList ########
$spWeb = Get-SPWeb -identity "http://sp2010server/TeamSite/"  #  Get SPWeb

$list = $spWeb.Lists["<My List Name>"] # Get SPList

foreach ($item in $list.items)
{
ExecuteSQLInsert $item["Title"].replace("'","''") $item["splistColumn2"].replace("'","''") $item["splistColumn3"].replace("'","''") $item["splistColumn4"].replace("'","''") $item["splistColumn5"].replace("'","''")
}

$spQuery = New-Object Microsoft.SharePoint.SPQuery
$spQuery.ViewAttributes = "Scope='Recursive'";
$spQuery.RowLimit = 2000
$caml = '<OrderBy Override="TRUE"><FieldRef Name="ID"/></OrderBy>'
$spQuery.Query = $caml

do
{
$listItems = $list.GetItems($spQuery)
$spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
foreach($item in $listItems)
{
ExecuteSQLInsert $item["Title"].replace("'","''") $item["splistColumn2"].replace("'","''") $item["splistColumn3"].replace("'","''") $item["splistColumn4"].replace("'","''") $item["splistColumn5"].replace("'","''")
}
}
while ($spQuery.ListItemCollectionPosition -ne $null)
$connection.Close()

$spWeb.Dispose()

 

...and finally populate the target SQL Table using the data from the SPListItem
(Obviously, you will need to give some consideration in relation to matching the 'DataTypes' of the SQL Columns, with those of the source SPList)

######### ExecuteSQLInsert function #############
function ExecuteSQLInsert($sqlVAL1, $sqlVAL2, $sqlVAL3, $sqlVAL4, $sqlVAL5)
{

$ErrorActionPreference = 'stop' # Prepare script for stopping
$SqlCmd.CommandText ="INSERT INTO [sqlTABLE] ([sqlCOLUMN1], [sqlCOLUMN2], [sqlCOLUMN3], [sqlCOLUMN4], [sqlCOLUMN5]) VALUES ('$sqlVAL1', '$sqlVAL2', '$sqlVAL3', '$sqlVAL4', '$sqlVAL5')" # Configure TSQL

Try
{
$SqlCmd.ExecuteNonQuery() #
}
Catch
{
Write-Warning "$_" # Report Errors
Write-Warning $SqlCmd.CommandText
Write-host
}

}

 

download  SP2010ImportExportSPListToSQL - Examples.zip

 

 

Published by aobi

Comments

 

Daniel said:

thank you very much for your article - very helpful

I was wondering how i would go about specifying only list items which fulfil a certain criteria - in my case a date field needs to be lower than today - 1 and how i could delete list items once they have been exported into sql

any ideas?

thank you

Daniel

January 15, 2013 11:11 AM