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, it could also 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 the given SharePoint List(s), iterates through all available fields, dynamically creates an SQL Server Table, and then inserts the data after translating it and inserts it 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, it’s “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 - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

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

  • Any suggestion how to get also version comments ($item.Versions -> $version[‘Answer’]) to a column in sql table ?

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

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

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

    Reply
  • 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
  • thank you for this script , how about large list or threshold limit (5000) ?

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

    Reply
    • 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
  • 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
    • Under the “Switch” section, add one more entry for “dependentlookupfields”

      Reply
    • 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
  • 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
    • Just to confirm: You are running the script from any of your SharePoint server, isn’t it?

      Reply
  • 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
  • Is there a way to make this work with calculated fields?

    Reply
    • 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
  • Can this be modified to copy calculated fields as well?

    Reply
    • 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
  • 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
    • Say: $UserValue=”1;#username”
      $Arr = $UserValue.Split(“;#”);
      $UserName = $Arr[2];

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *