Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
Highlighted
andreicatalin
New Contributor II

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:

URL: https://domain/subsite1/subsite2/_api/web/GetFolderByServerRelativeUrl(' folder1/folder2/3.0 folder3/1.0 folder4/test_data’)/Files 

Method: GET

Authentication: Windows NTLM

Connects fine to above. No pagination settings yet. What I can retrieve from the above is in effect a list of URLs for the files present there, as such:

https://domain/subsite1/subsite2/_api/Web/GetFileByServerRelativeUrl('/subsite1/subsite2/ folder1/folder2/3.0 folder3/1.0 folder4/test_data /exampleExcelFile1.xlsx')

https://domain/subsite1/subsite2/_api/Web/GetFileByServerRelativeUrl('/subsite1/subsite2/ folder1/folder2/3.0 folder3/1.0 folder4/test_data /exampleExcelFile2.xlsx')

etc.

I understand that one can't use a wildcard load, nor does a construct like the below work:

 Let vExcelFilePath = 'lib://[SharePoint Path]/test_data/*.xlsx';

For Each vFile In FileList('$(vExcelFilePath)')

Trace $(vFile);

exampleTable:

LOAD
    "Project ID" as d_ProjectID,
    "Project Name" as d_ProjectName

   //whatever other fields etc.

      From [$(vFile)](ooxml, embedded labels, table is ProjectData);
 
Next vFile

My logic was to use the list pulled through the rest connector, loop through the ‘n’ number of excel URLs and load them via a looped Web File connection.

 

But as I try to connect to, say,

 

https://domain/subsite1/subsite2/_api/Web/GetFileByServerRelativeUrl('/subsite1/subsite2/ folder1/folder2/3.0 folder3/1.0 folder4/test_data /exampleExcelFile1.xlsx')

 

error.png

 

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.

 

Thanks

 

1 Reply
Support
Support

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.