Skip to main content
Sept. 27, 10AM ET Do more with Qlik, Insight Advisor – our intelligent AI-assistant in Qlik Sense: Register
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Extracting data from Spreadsheet in SharePoint Cloud

We currently have SharePoint services on premise and are migrating out sited to the Azure Cloud, We have some content kept in XLSX Spreadsheets stored in our on premise sites. I have done some testing and research and cannot seem to identify the proper method of connecting to the SharePoint cloud for extraction of data from a Spreadsheet, I have looked at the WebConnector and see an Office 365 Web Connector that is in Beta development and I really don't want to consider a Beta solution for this problem.

Any assistance is appreciated,

1 Reply
Partner - Creator
Partner - Creator

As a follow up to my question I have resolved this issue by creating PowerShell scripts to extract the SharePoint Cloud spreadsheets and place them on a local drive for QlikView to consume rather then extracting directly from SharePoint. Below is a sample script and a link to Microsoft instructions.

I hope this helps!!!!

# Requirements:
# Follow the install instructions here:
# As a first time setup (and whenever the password you are using changes, you must create a secure password using the following line:
# read-host -assecurestring | convertfrom-securestring | out-file 'C:\work\username.txt'

# Setup
$UserName = ""
$Password = Get-Content 'C:\work\user.txt' | ConvertTo-SecureString
$Credential = New-Object System.Management.Automation.PsCredential($UserName, $Password)

# For each of your sites you want to download from, call this
# NOTE: you must put the full path to the site you wish to download the file from
Connect-PnPOnline -Url -Credentials $Credential
# Then call this to download each file
# NOTE: you must put the path to the file without the server here
Get-PnPFile -Url "/sites/pagename/Shared%20Documents/Test.xlsx" -Path c:\work -FileName "TestGetFile.xlsx" -AsFile -Force