Read SharePoint List and write to MS SQL Server using PowerShell

Problem Statement: We had a scenario where SharePoint List data need to be used for Reporting purpose but the reporting Tool was not able to connect directly to SharePoint List Data source.


Solution :  Reporting tool was able to connect to MS SQL server so we were advised to use MS SQL server as data source for the reporting. Now the challenge was how we can move the SharePoint List data to MS SQL table in regular basis. We had option with MS flow with On-primise data gatway connector but we did not get approval to Install the Data gateway on our local servers.

So we have come with Solution with simple PowerShell script which will run on a schedule and move the SharePoint List data to MS SQL Table.

Below is code snippet of the PowerShell

#This script to pull the List data and move into SQL for Reporting





$logFile = "C:\ListToSQL\LogFile-PROD.txt"  // log file path to store the Errors and normal logging
$date=Get-Date 
Add-Content $logFile "Starting to Process $date"
# SharePoint Credentials to connect the List
$O365ServiceAccount="sharepointsiteadmin@abc.com"
$O365ServiceAccountPwd="ABC2020" // Password
$siteURL="https://abc.sharepoint.com/sites/abc"
[SecureString]$SecurePass = ConvertTo-SecureString $O365ServiceAccountPwd -AsPlainText -Force 
[System.Management.Automation.PSCredential]$PSCredentials = New-Object System.Management.Automation.PSCredential($O365ServiceAccount, $SecurePass) 
Connect-PnPOnline -Url $siteURL -Credentials $PSCredentials

#Sharepoint List Connected

$List="Tableau Archive Data"
#Get List items where ComreferenceId is equal to Null
$Items=Get-PnPListItem -List $List -Query "<View><Query><Where><IsNull><FieldRef Name='comReferenceId'/></IsNull></Where></Query></View>" 

#Loop through the items
foreach($Item in $Items)
{
 $ID=$Item["ID"]
 $MovedToSQL=$Item["MovedToSQL"]


 # check if item already moved to SQL
 if($MovedToSQL -ne "Yes")
 {
 try{
  Add-Content $logFile "Processing $ID"
 # Get all Properties of a List Item
  $comRequesterName=$Item["comRequesterName"]
  if($comRequesterName.length -ne 0){
  $comRequesterName=$comRequesterName.Replace("'","''")
  }
  $comRequesterId=$Item["comRequesterId"]
    if($comRequesterId.length -ne 0){
      $comRequesterId=$comRequesterId.Replace("'","''")
      }
  $comTypeOfCompliance=$Item["comTypeOfCompliance"]
  $comBusinessUnit=$Item["comBusinessUnit"]
  $comTimeTakenHours=$Item["comTimeTakenHours"]
  $comTimeTakenMinutes=$Item["comTimeTakenMinutes"]
  $comDeadlineDate=$Item["comDeadlineDate"]
    $comRequestCategoryName=$Item["comRequestCategoryName"]
  $comRequestCategoryCode=$Item["comRequestCategoryCode"]
    if($comRequestCategoryCode.length -gt 3)
    {
    $comRequestCategoryCode=$comRequestCategoryCode.Substring(0,3)
    }

    $comRequestTitle=$Item["comRequestTitle"]
    $comRequestDate=$Item["comRequestDate"]
    $comRequestDescription=$Item["comRequestDescription"]
    if($comRequestDescription.length -ne 0){
      $comRequestDescription=$comRequestDescription.Replace("'","''")
      }
    $comAssigneeName=$Item["comAssigneeName"]
    $comRequestPriority=$Item["comRequestPriority"]
    $comRequestStatus=$Item["comRequestStatus"]
    $comProcessInstanceId=$Item["comProcessInstanceId"]
    $comRequestStatus=$Item["comRequestStatus"]
    $path= $siteURL + "/Lists/" + $List + "/dispform.aspx?id=" + $ID
    $comComments=$Item["comComments"]
     if($comComments.length -ne 0){
      $comComments=$comComments.Replace("'","''")
      }
    $Created=$Item["Created"]
    $Author=$Item["Author"].LookupValue
    $Requestguid=[guid]::newguid()
    $commentguid=[guid]::newguid()

   # Insert query for Compliance Requests SQL Table

    $insertquery=" 
    INSERT INTO [Compliance].[Requests]
           ([comReferenceId]
        
           ,[comRequesterName]
           ,[comRequesterEmail]
           ,[comTypeOfCompliance]
           ,[comBusinessUnit]
          
           ,[comDocumentOwnerName]
           ,[comDocumentOwnerEmail]
           ,[comDeadlineDate]
           ,[comRequestCategoryCode]
           ,[comRequestCategoryName]
           ,[comRequestTitle]
           ,[comRequestDate]
           ,[comRequestDescription]
           ,[comAssigneeName]
           ,[comRequestPriority]
           ,[comRequestStatus]
         
           ,[comProcessInstanceId]
           ,[comK2Viewflow]) 
     VALUES 
           ('$Requestguid'
   
           ,'$comRequesterName'
           ,'$comRequesterId' 
           ,'$comTypeOfCompliance'
           ,'$comBusinessUnit' 
     
           ,'$comTimeTakenHours' 
           ,'$comTimeTakenMinutes'
           ,'$comDeadlineDate' 
           ,'$comRequestCategoryCode'
           ,'$comRequestCategoryName' 
           ,'$comRequestTitle'
           ,'$comRequestDate' 
           ,'$comRequestDescription'
           ,'$comAssigneeName' 
           ,'$comRequestPriority'
           ,'$comRequestStatus' 
        
           ,'$comProcessInstanceId'  
           ,'$path') 
    GO 
    " 

Invoke-SQLcmd -ServerInstance 'ServerName\DBInstanceName' -query $insertquery -Database DatabaseName


}
 Set-PnPListItem -List $List -Identity $Item.Id -Values @{ "MovedToSQL"="Yes"}
 Add-Content $logFile    "Processed $ID" 
 }
 catch{
 # write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
   Add-Content $logFile    $_.Exception.Message
   #Send email to Vinit if any error occured during the update
   Send-PnPMail -To vinit.kumar@abc.com -Subject test -Body "$ID Error : $($_.Exception.Message)"
 }
 }

}
 $date=Get-Date 
Add-Content $logFile "Process Completed on $date"

Please comment if you find any difficulties in executing this.
Happy Coding
:)