topic Load data from multiple xlsx files from SharePoint folder in Connectivity & Data Prep

Load data from multiple xlsx files from SharePoint folder

andreicatalin — Wed, 22 Dec 2021 14:19:47 GMT

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')

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

Re: Load data from multiple xlsx files from SharePoint folder

Vu_Nguyen — Tue, 07 May 2019 12:54:11 GMT

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.

Re: Load data from multiple xlsx files from SharePoint folder

punctum — Wed, 20 Nov 2019 13:56:31 GMT

Hi, I like your explicite and detailed answer, but what about a soulution?

Qlik Sense, in sharepoint files are uploaded monthly, each with a new filename (various extensions) . How to load that data.

REST Connector, Web Connector?

The files have to be read only once.

Re: Load data from multiple xlsx files from SharePoint folder

AswainKumar — Fri, 04 Dec 2020 17:01:01 GMT

Any body got answer to above requirement,i am also facing issue with same requirement as above.I have web connector and able to create connection to single file.But i need to load data from multiple excel file with one connection

Re: Load data from multiple xlsx files from SharePoint folder

punctum — Fri, 04 Dec 2020 18:20:30 GMT

Hi!
Map sharepoint to a regular Drive or directory and use normal file access.
Read filenames into a variable and perform a loop on FieldValueList('xx')
That works. (easy)
Greetings,
Hartmut Schulte
Punctum KG