kwizcom banner advertisement

Export SharePoint List Data to SQL Server Table using PowerShell

Requirement:
For an in-house built business intelligence tool, had a requirement to export SharePoint list data into SQL Server table. As directly querying SharePoint content databases is not supported by Microsoft and it also could cause performance issues to your SharePoint farm - Lets not think about it further! Of course, there are 3rd party products available on the market to do this, however PowerShell can be utilized to fulfill this requirement.

This PowerShell script retrieves given SharePoint List(s), iterate through all available fields and dynamically creates SQL Server Table and then inserts the data after translating it, and inserts into the SQL Server database table.
Export SharePoint List Data to SQL Server Table using PowerShell

Here is my PowerShell script to Convert SharePoint list into SQL Server table and extract data from SharePoint to SQL:

PowerShell Script to Extract SharePoint List Data into SQL Server Table:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue 

Function Create-List2Table($ListName,$TableName, $WebURL)
{
#Configuration Variables
$DatabaseServer="SP16-SQLServer01"
$DatabaseName="SharePointBI"
#SQL Authentication 
$UserID="UserID"
$Password="Password"

#Log File
$CurrentDir=Split-Path -Parent -Path $MyInvocation.MyCommand.Definition
$LogFile = $CurrentDir+ "\" + $(Get-Date -format "yyyyMMdd_hhmmss")+".txt"

#Get Web, List and Fields
$Web= Get-SPWeb $WebURL
$List= $Web.Lists[$ListName]
#Get all required fields from the lists 
$ListFields = $List.Fields | Where { ($_.Hidden -ne $true ) -and ($_.ReadOnlyField -ne $true ) -and ($_.InternalName -ne "Attachments") -and ($_.InternalName -ne "ContentType") }

#Function to Execute given SQL Query
Function Execute-SQLQuery([string]$Query)
{
    #Write-Host "Executing SQL: ", $Query
    #Connection object
    $Connection = New-Object System.Data.SqlClient.SqlConnection("server=$DatabaseServer;database=$DatabaseName;User Id=$UserID;Password=$Password")
    try
    {
        $Connection.open() 
        $cmd = new-object System.Data.SqlClient.SqlCommand ($Query, $Connection)
        $ReturnValue = $cmd.ExecuteNonQuery()
    }
    catch  { Write-Host "Error: ", $error[0];  "$($Query): $($_.Exception.Message)" >> $LogFile }
    finally{ $Connection.close() }

}

#Function to Drop Table, if exists!
Function Drop-Table([string]$TableName)
{
  $Query = "IF (OBJECT_ID('[dbo].[$($TableName)]','U') IS NOT NULL) DROP TABLE [dbo].[$($TableName)]"
  
  #Run the Query 
  Execute-SQLQuery $Query
}

#Get SQL column Definition for SharePoint List Field
Function Get-ColumnDefinition([Microsoft.SharePoint.SPField]$Field) 
{
    $ColumnDefinition=""
    Switch($Field.Type)
    {
      "Boolean" { $ColumnDefinition = '['+ $Field.InternalName +'] [bit] NULL '}
      "Choice" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar](MAX) NULL '}
      "Currency" { $ColumnDefinition = '['+ $Field.InternalName +'] [decimal](18, 2) NULL '}
      "DateTime" { $ColumnDefinition = '['+ $Field.InternalName +'] [datetime] NULL '}
      "Guid" { $ColumnDefinition = '['+ $Field.InternalName +'] [uniqueidentifier] NULL '}
      "Integer" { $ColumnDefinition = '['+ $Field.InternalName +'] [int] NULL '}
      "Lookup" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (500) NULL '}
      "MultiChoice" { $ColumnDefinition = '['+ $Field.InternalName +'] [nText] (MAX) NULL '}
      "Note" { $ColumnDefinition = '['+ $Field.InternalName +'] [nText] NULL '}
      "Number" { $ColumnDefinition = '['+ $Field.InternalName +'] [decimal](18, 2) NULL '}
      "Text" { $ColumnDefinition = '['+ $Field.InternalName +'] [nVarchar] (MAX) NULL '}
      "URL" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (500) NULL '}
      "User" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (255) NULL '}      
      default { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (MAX) NULL '}
  }
  return $ColumnDefinition
}

################ Format Column Value Functions ######################
Function Format-UserValue([object] $ValueToFormat)
{
     $Users = [string]::join("; ",( $ValueToFormat | Select -expandproperty LookupValue))
     $Users = $Users -replace "'", "''"
     return "'" + $Users + "'"
}

Function Format-LookupValue([Microsoft.SharePoint.SPFieldLookupValueCollection] $ValueToFormat)
{
     $LookupValue = [string]::join("; ",( $ValueToFormat | Select -expandproperty LookupValue))
     $LookupValue = $LookupValue -replace "'", "''"
     return "'" + $LookupValue + "'"
}

Function Format-DateValue([string]$ValueToFormat)
{
  [datetime] $dt = $ValueToFormat
  return "'" + $dt.ToString("yyyyMMdd HH:MM:ss") + "'"
}

Function Format-MMSValue([Object]$ValueToFormat)
{
  return "'" + $ValueToFormat.Label + "'"
}

Function Format-BooleanValue([string]$ValueToFormat)
{
  if($ValueToFormat -eq "Yes") {return 1} else { return 0}
}

Function Format-StringValue([object]$ValueToFormat)
{
  [string]$result = $ValueToFormat -replace "'", "''"
  return "'" + $result + "'"
}

#Function to get the value of given field of the List item
Function Get-ColumnValue([Microsoft.SharePoint.SPListItem] $ListItem, [Microsoft.SharePoint.SPField]$Field) 
{
    $FieldValue= $ListItem[$Field.InternalName]
    
    #Check for NULL
    if([string]::IsNullOrEmpty($FieldValue)) { return 'NULL'}
    
    $FormattedValue = ""
    
    Switch($Field.Type)
    {
    "Boolean"  {$FormattedValue =  Format-BooleanValue($FieldValue)}
    "Choice"  {$FormattedValue = Format-StringValue($FieldValue)}
    "Currency"  {$FormattedValue = $FieldValue}
    "DateTime"  {$FormattedValue = Format-DateValue($FieldValue)}
    "Guid" { $FormattedValue = Format-StringValue($FieldValue)}
    "Integer"  {$FormattedValue = $FieldValue}
    "Lookup"  {$FormattedValue = Format-LookupValue($FieldValue) }
    "MultiChoice" {$FormattedValue = Format-StringValue($FieldValue)}
    "Note"  {$FormattedValue = Format-StringValue($Field.GetFieldValueAsText($ListItem[$Field.InternalName]))}
    "Number"    {$FormattedValue = $FieldValue}
    "Text"  {$FormattedValue = Format-StringValue($Field.GetFieldValueAsText($ListItem[$Field.InternalName]))}
    "URL"  {$FormattedValue =  Format-StringValue($FieldValue)}
    "User"  {$FormattedValue = Format-UserValue($FieldValue) } 
     #Check MMS Field
     "Invalid" { if($Field.TypeDisplayName -eq "Managed Metadata") { $FormattedValue = Format-MMSValue($FieldValue) } else { $FormattedValue =Format-StringValue($FieldValue)}  }
    default  {$FormattedValue = Format-StringValue($FieldValue)}
  }
  Return $FormattedValue
}

#Create SQL Server table for SharePoint List
Function Create-Table([Microsoft.SharePoint.SPList]$List)
{
    #Check if the table exists already
    $Query="CREATE TABLE [dbo].[$($TableName)]([ID] [int] NOT NULL PRIMARY KEY, "
    foreach ($Field in $ListFields)
    {
        $Query += Get-ColumnDefinition($Field) 
        $Query += ","
    }
    $Query += ")"

    #Run the Query   
    Execute-SQLQuery $Query  
}

#Insert Data from SharePoint List to SQL Table
Function Insert-Data([Microsoft.SharePoint.SPList]$List)
{ 
    #Iterate through each row from the list 
    $ListItems= $List.Items # | where {$_["ID"] -eq 820}

    #Progress bar counter
    $Counter=0
    $ListItemCount=$ListItems.Count
    
    Write-host "Total SharePoint List Items to Copy:" $ListItemCount    
    foreach ($Item in $ListItems) 
    {

        Write-Progress -Activity "Copying SharePoint List Items. Please wait...`n`n" -status "Processing List Item: $($Item['ID'])" -percentComplete ($Counter/$ListItemCount*100)

        $sql = new-object System.Text.StringBuilder
        [void]$sql.Append("INSERT INTO [dbo].[$($TableName)] ( [ID] ")
        $vals = new-object System.Text.StringBuilder
        [void]$vals.Append("VALUES ("+ $Item["ID"])
        
        foreach ($Field in $ListFields) 
        {
            [void]$sql.Append(",[$($Field.InternalName)]")
            $ColumnValue =  Get-ColumnValue $Item $Field
            [void]$vals.Append( ","+ $ColumnValue)
        }
       
    [void]$sql.Append(") ")
    [void]$vals.Append(") ")
    
    #Combine Field and Values
    $SQLStatement = $sql.ToString() + $vals.ToString()
    
    #Run the Query   
    Execute-SQLQuery $SQLStatement 
    
    $Counter=$Counter+1;  
    }
"Total SharePoint List Items Copied: $($ListItemCount)" >> $LogFile
}

 #Call functions to export-import SharePoint list to SQL table
 Drop-Table $TableName
 Create-Table $List
 Insert-Data $List
}

#Call the function to Create SQL Server Table from SharePoint List
Create-List2Table -ListName "Projects" -TableName "ProjectData" -WebURL "https://portal.crescent.com/projects"
#Create-List2Table -ListName "Documents" -TableName "Documents" -WebURL "https://portal.crescent.com"

Make sure you have the database created (in my case, its "SharePointBI") and the User Name provided in the script has DBO or similar access on the database before running the script.

Credits: My sincere thanks to http://www.terrymarshall.com.au/Blog/tabid/162/EntryId/164/SharePoint-2010-Exporting-Lists-to-SQL-Server-Part-1.aspx
Export SharePoint List Data to SQL Server Table using PowerShell Export SharePoint List Data to SQL Server Table using PowerShell Reviewed by Salaudeen Rajack on 2:28 PM Rating: 5

14 comments:

  1. When I execute this it has an issue trying convert the $users to a string. We use Okta and an example of the value being returned prior to the conversion is:

    24;#John Doe

    Any ideas how to get around this?

    ReplyDelete
    Replies
    1. Say: $UserValue="1;#username"
      $Arr = $UserValue.Split(";#");
      $UserName = $Arr[2];

      Delete
  2. Can this be modified to copy calculated fields as well?

    ReplyDelete
    Replies
    1. Yes, The default switch should take care of it. If you want to handle it differently, Under the "Switch" section, add one more entry for "Calculated"

      Delete
  3. Is there a way to make this work with calculated fields?

    ReplyDelete
    Replies
    1. Calculated fields are taken as "text" in this script. If you want a specific data type, you can check for field type as "Calculated" and set the data type in SQL accordingly.

      Delete
  4. i run the script
    Powershell.exe -executionpolicy remotesigned -File C:\Users\SE\Desktop\ps.ps1
    but error occur like
    Split-path: cannot find drive.
    How can i solve it?

    ReplyDelete
  5. Hi,
    Thanks for the amazing script. But when I am trying to use it, it throws exception saying unable to find Unable to find type [Microsoft.SharePoint.SPList]. I have also used
    Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
    Add-Type -AssemblyName System.Web

    Not sure what is missing here. Could you please help.
    Thanks,
    Abhinav

    ReplyDelete
    Replies
    1. Just to confirm: You are running the script from any of your SharePoint server, isn't it?

      Delete
  6. Hi Salaudeen,
    First of all thanks a lot for this amazing script. The issue I have is its not picking up dependentlookupfields. How can we make that work?

    ReplyDelete
    Replies
    1. Under the "Switch" section, add one more entry for "dependentlookupfields"

      Delete
    2. I tried that by adding "dependentlookupfields" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (500) NULL '} under Switch($Field.Type). It does not work. It also does not copy 'Created/modified by' fields.

      Delete
  7. If you want the display name of the SharePoint list field as your SQL column name, replace .InternalName with .Title throughout the script.

    ReplyDelete
    Replies
    1. That's right. But the problem with DisplayName is: Duplicates! Two or more fields may have same display name and that would result error on table creation (You can't have a same field name twice!)

      Delete

Please Login and comment to get your questions answered!

Powered by Blogger.