Export SharePoint List Data to SQL Server Table using PowerShell

Requirement: Export SharePoint list data to SQL Server table.

For an in-house built business intelligence tool, We had a requirement to export SharePoint list data into an 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 – Let’s 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=[string]::Empty
    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 = [string]::Empty
    
    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.

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

20 thoughts on “Export SharePoint List Data to SQL Server Table using PowerShell

  • June 8, 2020 at 4:01 PM

    I am having trouble getting the “User ID” associated with an added record. Any advice will be appreciated

    Reply
  • October 29, 2019 at 12:11 AM

    Lovely script. Did you ever have any luck with the dependent look up fields Salaudeen?

    Reply
  • August 13, 2019 at 8:52 PM

    Saludeen,
    How can I modify the script to use Windows Authentication, please?

    Reply
  • August 1, 2019 at 10:16 AM

    powershell.exe : out-file : Cannot find drive. A drive with the name ‘param($ListName,
    At line:1 char:1
    Thanks for this, it’s all working but I have some errors, should I be concerned?:

    + powershell.exe -File .SP2SQLListInsert.ps1 -Listname “Overview” – …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (out-file : Cann…ram($ListName, :String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError

    Reply
  • December 29, 2018 at 8:19 PM

    thank you for this script , how about large list or threshold limit (5000) ?

    Reply
  • July 21, 2018 at 4:00 PM

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

    Reply
    • July 24, 2018 at 8:03 AM

      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!)

      Reply
  • July 2, 2018 at 9:21 PM

    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?

    Reply
    • July 5, 2018 at 12:17 PM

      Under the “Switch” section, add one more entry for “dependentlookupfields”

      Reply
    • July 5, 2018 at 6:49 PM

      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.

      Reply
  • July 2, 2018 at 9:01 AM

    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

    Reply
    • July 5, 2018 at 12:16 PM

      Just to confirm: You are running the script from any of your SharePoint server, isn’t it?

      Reply
  • December 7, 2017 at 4:55 AM

    i run the script
    Powershell.exe -executionpolicy remotesigned -File C:UsersSEDesktopps.ps1
    but error occur like
    Split-path: cannot find drive.
    How can i solve it?

    Reply
  • August 29, 2017 at 4:51 PM

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

    Reply
    • August 30, 2017 at 6:47 AM

      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.

      Reply
  • August 29, 2017 at 3:41 PM

    Can this be modified to copy calculated fields as well?

    Reply
    • July 5, 2018 at 12:20 PM

      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”

      Reply
  • January 21, 2017 at 12:25 AM

    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?

    Reply
    • January 29, 2017 at 11:19 AM

      Say: $UserValue=”1;#username”
      $Arr = $UserValue.Split(“;#”);
      $UserName = $Arr[2];

      Reply

Leave a Reply