Extracting data from Spreadsheet in SharePoint Cloud

Anonymous — Mon, 11 Dec 2017 22:03:36 GMT

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,

Re: Extracting data from Spreadsheet in SharePoint Cloud

dbaksa_1 — Thu, 15 Feb 2018 15:46:03 GMT

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:
# https://msdn.microsoft.com/en-us/pnp_powershell/pnp-powershell-overview
#
# 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 = "username@xxxxxx.com"
$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 https://xxxxxx.sharepoint.com/sites/username -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

topic Re: Extracting data from Spreadsheet in SharePoint Cloud in QlikView

Extracting data from Spreadsheet in SharePoint Cloud

Re: Extracting data from Spreadsheet in SharePoint Cloud