Programmatically extract SharePoint list data to a Database using PowerShell"




If you're a SharePoint Developer or Administrator you often need to give business user access to the data in SharePoint. The problem is the bigger these lists get the slower it is to retrieve the data via external tools and apps like Excel. So the best approach is to run a PowerScript that does this for you. I created the below script to run these types for processes in my environment. ( Which I have a lot of. ) How it works: When you call the ExtractList2Table function you need to pass a few parameters like the list name, the destination database server name, the name of the DB , the name of the main table where the data will be stored (script will create this), the staging table name where changes will be written to before it is committed to the main table (script will create this) and lastly the web URL.
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
Function ExtractList2Table($ListName,$DBServer,$DBName,$MainTable,$StagingTable,$WebURL)
{
    #Configuration Variables
    $DatabaseServer = $DBServer
    $DatabaseName= $DBName
    function sendmail([string]$Subject, [string]$Body)
    {
       Send-MailMessage -SmtpServer "YOUR SMTP ADDRESS" -To "YOUR MAIL ADDRRESS" -From "YOUR MAIL ADDRRESS" -Subject $Subject -Body $Body
    }

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

    #Get SQL column Definition for SharePoint List Field
    Function Get-ColumnDefinition([Microsoft.SharePoint.SPField]$Field)
    {
        $ColumnDefinition=""
        Switch($Field.Type)
        {
        "Boolean" { $ColumnDefinition = '['+ $Field.Title +'] [bit] NULL '}
        "Choice" { $ColumnDefinition = '['+ $Field.Title +'] [nvarchar](MAX) NULL '}
        "Currency" { $ColumnDefinition = '['+ $Field.Title +'] [decimal](18, 2) NULL '}
        "DateTime" { $ColumnDefinition = '['+ $Field.Title +'] [datetime] NULL '}
        "Guid" { $ColumnDefinition = '['+ $Field.Title +'] [uniqueidentifier] NULL '}
        "Integer" { $ColumnDefinition = '['+ $Field.Title +'] [int] NULL '}
        "Lookup" { $ColumnDefinition = '['+ $Field.Title +'] [nvarchar] (500) NULL '}
        "MultiChoice" { $ColumnDefinition = '['+ $Field.Title +'] [nText] (MAX) NULL '}
        "Note" { $ColumnDefinition = '['+ $Field.Title +'] [nText] NULL '}
        "Number" { $ColumnDefinition = '['+ $Field.Title +'] [decimal](18, 2) NULL '}
        "Text" { $ColumnDefinition = '['+ $Field.Title +'] [nVarchar] (MAX) NULL '}
        "URL" { $ColumnDefinition = '['+ $Field.Title +'] [nvarchar] (500) NULL '}
        "User" { $ColumnDefinition = '['+ $Field.Title +'] [nvarchar] (255) NULL '}
        default { $ColumnDefinition = '['+ $Field.Title +'] [nvarchar] (MAX) NULL '}
    }
    return $ColumnDefinition
    }
    ################ Format Column Value Functions ######################
    Function Format-UserValue([object] $ValueToFormat)
    {
        if([String]::IsNullOrEmpty($ValueToFormat) -eq $false) {
            $Users = $ValueToFormat.Substring($ValueToFormat.IndexOf("#") + 1)
            return "'" + $Users + "'"
        } else {
            write-host $ValueToFormat
            return "'NULL'";
        }
    }
    Function Format-LookupValue([Microsoft.SharePoint.SPFieldLookupValueCollection] $ValueToFormat)
    {
        $LookupValue = [string]::join("; ",( $ValueToFormat | Select-Object -expandproperty LookupValue))
        $LookupValue = $LookupValue -replace "'", "''"
        return "'" + $LookupValue + "'"
    }
    Function Format-DateValue([string]$ValueToFormat)
    {
        [datetime] $dt = $ValueToFormat
        return "'" + $dt + "'"
    }
    Function Format-CurrencyValue([string]$ValueToFormat)
    {
        [decimal] $dc = $ValueToFormat
        return "'" + $dc + "'"
    }
    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.Title]
        #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 = Format-CurrencyValue($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.Title]))}
        "Number"    {$FormattedValue = $FieldValue}
        "Text"  {$FormattedValue = Format-StringValue($Field.GetFieldValueAsText($ListItem[$Field.Title]))}
        "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 CreateMainTable([Microsoft.SharePoint.SPList]$List)
    {
        #Check if the table exists already
        $TableCheckQuery = "Select OBJECT_ID('[dbo].[$($MainTable)]','U')"
        $Result = Invoke-Sqlcmd -ServerInstance $DatabaseServer -Database $DatabaseName -Query $TableCheckQuery -querytimeout 300

        if([String]::IsNullOrEmpty($Result.Column1.ToString()))
        {
            Write-Host "Creating table"
            #Create the table
            $Query="CREATE TABLE [dbo].[$($MainTable)]("
            foreach ($Field in $ListFields)
            {
                $Query += Get-ColumnDefinition($Field)
                $Query += ","
            }
            $Query += ")"

            #Run the Query
            Invoke-Sqlcmd -ServerInstance $DatabaseServer -Database $DatabaseName -Query $Query -querytimeout 300
        }
    }
    Function CreateStagingTable([Microsoft.SharePoint.SPList]$List)
    {
        #Check if the table exists already
        $TableCheckQuery = "Select OBJECT_ID('[dbo].[$($StagingTable)]','U')"
        $Result = Invoke-Sqlcmd -ServerInstance $DatabaseServer -Database $DatabaseName -Query $TableCheckQuery -querytimeout 300

        if([String]::IsNullOrEmpty($Result.Column1.ToString()))
        {
            Write-Host "Creating table"
            #Create the table
            $Query="CREATE TABLE [dbo].[$($StagingTable)]("
            foreach ($Field in $ListFields)
            {
                $Query += Get-ColumnDefinition($Field)
                $Query += ","
            }
            $Query += ")"

            #Run the Query
            Invoke-Sqlcmd -ServerInstance $DatabaseServer -Database $DatabaseName -Query $Query -querytimeout 300
        }
    }

    #Insert Data from SharePoint List to SQL Table
    Function InsertData([Microsoft.SharePoint.SPList]$List)
    {
        #clear Staging table
        Invoke-Sqlcmd -ServerInstance $DatabaseServer -Database $DatabaseName -Query "Delete from [dbo].[$($StagingTable)]" -querytimeout 300
        $endDate = Get-Date -Format "yyyy-MM-ddTHH:mm:ssZ"
        #Get the last timestamp when data was collected from the SharePoint List
        $MaxDate = Invoke-Sqlcmd -ServerInstance $DatabaseServer -Database $DatabaseName -Query "select max(Modified) FROM [dbo].[$($MainTable)]"
        if([String]::IsNullOrEmpty($MaxDate.Column1.ToString()))
        {
            $MaxDate = "2010-10-01T00:00:00Z"
        }
        else
        {
            $MaxDate = $MaxDate.Column1.ToString("yyyy-MM-ddTHH:mm:ssZ")
        }
        #Run caml query to select the items from SharePoint since the last run
        Write-host "Executing caml"
        $spQuery = New-Object Microsoft.SharePoint.SPQuery
        $spQuery.ViewAttributes = "Scope='Recursive'";
        $caml =
        '<Where>
            <And>
            <And>
            <Gt>
                <FieldRef Name="Modified" />
                <Value Type="DateTime" IncludeTimeValue="True">'+$MaxDate+'</Value>
            </Gt>
            <Leq>
                <FieldRef Name="Modified" />
                <Value Type="DateTime" IncludeTimeValue="True">'+$endDate+'</Value>
            </Leq>
            </And>
            <Eq>
                <FieldRef Name="FSObjType" />
                <Value Type="int">0</Value>
            </Eq>
            </And>
        </Where>'
        $spQuery.Query = $caml

        $ListItems=$List.GetItems($spQuery)

        #Progress bar counter
        $Counter=0
        $ListItemCount=$ListItems.Count

        Write-Host "Executing Inserting"
        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].[$($StagingTable)] (")
            $vals = new-object System.Text.StringBuilder
            [void]$vals.Append("VALUES (")

            $loop = 0
            foreach ($Field in $ListFields)
            {
                if($loop -gt 0)
                {
                    [void]$sql.Append(",")
                    [void]$vals.Append(",")
                }
                [void]$sql.Append("[$($Field.Title)]")
                $ColumnValue =  Get-ColumnValue $Item $Field
                [void]$vals.Append($ColumnValue)

                $loop += 1
            }

        [void]$sql.Append(") ")
        [void]$vals.Append(") ")

        #Combine Field and Values
        $SQLStatement = $sql.ToString() + $vals.ToString()

        #Run the Query
        $SQLStatement
        try {
            Invoke-Sqlcmd -ServerInstance $DatabaseServer -Database $DatabaseName -Query $SQLStatement -querytimeout 300
            }
        catch
            {
                $body =  $QueryStr + "`n" + $error[0].Exception
                sendmail "Insert - LoadSPData: Load from SharePoint Error" $body
            }
        $Counter += 1;
        }
        "Total SharePoint List Items Copied: $($ListItemCount)"
         if($ListItemCount -gt 0) {
            MergeData
         }
    }

    #Merge Data from taging table
    Function MergeData()
    {
        $data = Invoke-Sqlcmd -ServerInstance $DatabaseServer -Database $DatabaseName -Query "Select * from dbo.[$($StagingTable)] nolock" -querytimeout 300
        $TableColumns = $data | Get-Member | Where-Object {$_.membertype -eq 'property'} | Select-Object name

            $sql = new-object System.Text.StringBuilder
            [void]$sql.Append("MERGE [dbo].[$($MainTable)]  AS Main ")
            [void]$sql.Append("Using [dbo].[$($StagingTable)] AS Staging ")
            [void]$sql.Append("ON (Main.[ID] = Staging.[ID]) ")
            [void]$sql.Append("WHEN MATCHED THEN UPDATE SET ")

            $loop = 0
            foreach ($column in $TableColumns.name)
            {
                if($loop -gt 0)
                {
                    [void]$sql.Append(",")
                }
                [void]$sql.Append("Main.[$($column)] = Staging.[$($column)]")
                $loop += 1
            }

        [void]$sql.Append(" WHEN NOT MATCHED THEN ")
        [void]$sql.Append("INSERT( ")
            $loop = 0
            foreach ($column in $TableColumns.name)
            {
                if($loop -gt 0)
                {
                    [void]$sql.Append(",")
                }
                [void]$sql.Append("[$($column)]")
                $loop += 1
            }
        [void]$sql.Append(") ")
        [void]$sql.Append(" VALUES( ")
        $loop = 0
            foreach ($column in $TableColumns.name)
            {
                if($loop -gt 0)
                {
                    [void]$sql.Append(",")
                }
                [void]$sql.Append("Staging.[$($column)]")
                $loop += 1
            }
        [void]$sql.Append("); ")

        #Combine Field and Values
        $SQLStatement = $sql.ToString()

        #Run the Query
       # $SQLStatement

        try {
            Invoke-Sqlcmd -ServerInstance $DatabaseServer -Database $DatabaseName -Query $SQLStatement -querytimeout 300
            }
        catch
            {
                $body =  $QueryStr + "`n" + $error[0].Exception
                sendmail "Insert - LoadSPData: Load from SharePoint Error" $body
            }
    }

        #Call functions to export-import SharePoint list to SQL table
        <#  Drop-Table $MainTable #>

        CreateMainTable $List
        CreateStagingTable $List
        InsertData $List

}
ExtractList2Table -ListName "MySPList" -DBServer "DBServerName" -DBName "DBName" -MainTable "PrimaryTable" -StagingTable "Staging" -WebURL "http://myspsite"

If the tables does not exist the script will create it. ( Make sure the SharePoint account have permissions to create these tables ) You can schedule this script with any scheduling tool of your choice and run it as often as you want to keep the data up to date. It will only fetch the data from SharePoint that have changed since the last run. In a lot of cases I run this every minute, because it runs super fast.

Conclusion


A lot of credit must go to this post here I found the original script before I modified it, http://www.terrymarshall.com.au/Blog/tabid/162/EntryId/164/SharePoint-2010-Exporting-Lists-to-SQL-Server-Part-1.aspx And this one as well, http://www.sharepointdiary.com/2016/06/export-sharepoint-list-data-to-sql-server-table-using-powershell.html