Load data from multiple xlsx files from SharePoint folder
Hi there. I am going to try to be as descriptive as possible with this issue. I have noticed there are a number of threads on similar / tangential matters, but none really appeared to resolve the issue I am facing.
I am relatively new to Qlik Sense (or View for that matter).
As the title suggests, the scenario is the following:
I am developing an app that relies on data :
found in 'n' number of .xslx workbooks. These workbooks are structurally identical and from there the app loads 4 sheets, that, taken together form the bulk of the data set.
these sheets reside on an internal SharePoint location.
What I seek to achieve is be able to have a connection to the SharePoint folder and load the 4 sheets from the 'n' number of workbooks present there.
I have established a REST connection to the SharePoint in question as such:
So the connection works but for some reason it won’t open the contents of the xlsx file.
What am I missing here? And further what should I be doing to achieve this connection / load of data? I have been scouring resources up and down and have come up empty handed, so any help will be appreciated.
Re: Load data from multiple xlsx files from SharePoint folder
I think the problem here is authentication. Your first REST connector (which loads the list of Excel files) is successful with Windows NTLM authentication required by Sharepoint.
However, webfile connection (Load...From [$(vFile)]) does not support Windows NTLM authentication. Instead, it will try to reach the URL "https://domain/... /exampleExcelFile1.xlsx" with credentials of the service account running Qlik Sense Engine. It may then receive an http message saying authentication fails. Because the connection is looking for an Excel file, it flags this http response as "not contain any valid data".
Qlik REST connector does not support loading Excel files. If you are loading from Sharepoint 365, you may consider using Qlik Office 365 Sharepoint Connector of Qlik Web Connectors (requires a license). This connector handles all the Sharepoint authentication steps for you.
Vu Nguyen If a post helps resolve your issue, please mark the answer as correct.