Extract data from a SharePoint List to SQL Server via Excel and PowerShell

This article explains the steps required to use PowerShell to open and refresh an Excel table of data connected directly to a SharePoint list, then import the data into SQL Server.

The Excel data table should be connected directly to the SharePoint list with a data connection such as: (Provider=Microsoft.Office.List.OLEDB.2.0;Data Source="";ApplicationName=Excel;Version=12.0.0.0)

Using these steps, data from the SharePoint list can be downloaded and refreshed automatically.

1) Install the Office 2010 Data Access Components
Whether you have Office 2010 or Office 2013, you will need to install the Office 2010 Data Access Components, which contain the “Microsoft.ACE.OLEDB.12.0” provider.

2) Enable Distributed Queries and CMD Statement Execution
Change the settings for the SQL server database to allow distributed queries.

EXEC sp_configure 'allow updates', 1;
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE;
GO

3) Create folders to enable Desktop Interaction
Create this folder:
C:\Windows\System32\config\systemprofile\Dektop
And, if you’re on a 64-bit OS, create this folder in addition to the first one:
C:\Windows\SysWOW64\config\systemprofile\Desktop

4) Configure the following settings required to open Excel via a the SQL Server Service Account
The SQL Service account may not have permission to launch an instance of Excel.

  1. Go to “Component Services” > Computer > right-click “My Computer” > Select Properties > Select “COM Security” tab > Click “Edit Default” button in the “Launch and Activation Permissions” group box > Add the account that is used to run the SQL job.
  2. Under “Component Services” > “Computer” > “My Computer” > “DCOM Config” > Right-click “Microsoft Excel Application” and select “Properties” > Under “Identity” tab, select “This user” and enter the account above and its password.

5) Ensure that the IE security settings allow browsing the SharePoint site
Add the SharePoint site to the trusted sites list and set the Trusted Site Security Level to Low.

6) Create PowerShell Script to open and refresh File
Create a PowerShell file (in Notepad or another simple text editor) called OpenAndRefresh.ps1 – I saved mine in the location C:\OpenAndRefresh.ps1. Then, copy this text into it (make sure you update your file’s address, as mine is located at C:\Folder\File.xlsx) and save. This script will open your Excel file, refresh it, save it, and close it.

$ExcelApp = new-object -Com Excel.Application
$ExcelApp.Visible = $false
$ExcelApp.DisplayAlerts = $false

$Workbook = $ExcelApp.Workbooks.Open("C:\Folder\File.xlsx") $Workbook.RefreshAll() $Workbook.Save()

$ExcelApp.Quit()

#Change the reference count for our instance of Excel from 1 to 0. [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelApp)

#Delete the object reference Remove-Variable ExcelApp

7) Execute the SQL to run the PowerShell command and load the new data from excel
This code will run the PowerShell file that we created in step 6, with a check for errors. Once the PowerShell script is run, it will import the data into SQL Server. As before, make sure you have the correct file location of your Excel document as well as the worksheet name. Note that a dollar sign is added after the worksheet’s name.

DECLARE @RC int
DECLARE @PSOutput TABLE(ID int identity(1,1), PSOutpuCol nvarchar(255) null)

--Download File INSERT @PSOutput (PSOutpuCol) EXEC @RC = master..xp_cmdshell 'powershell.exe -file "C:\OpenAndRefresh.ps1" -nologo'

IF EXISTS ( SELECT * FROM @PSOutput WHERE PSOutpuCol LIKE '%Exception%' ) BEGIN SELECT * FROM @PSOutput ORDER BY id

DELETE @PSOutput

RAISERROR ('There was a problem downloading data.' -- Message text.
   ,16 -- Severity.
   ,5 -- State.
) WITH SETERROR, NOWAIT;

END ELSE BEGIN DELETE @PSOutput END

SELECT * INTO [dbo].[sheet] FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Folder\File.xlsx', 'SELECT * FROM [Sheet1$]')

Evan Schmidt

Published on 02/18/2014

Authored by Brian Pohl