article How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors in Official Support Articles

How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

Sonja_Bauernfeind — Tue, 17 Nov 2020 08:33:01 GMT

In this example, we load data from an Excel file hosted on SharePoint 365 using the Qlik Office 365 Sharepoint connector.

Environment:

Qlik Web Connectors: Qlik Office 365 SharePoint Connector
All Qlik Sense Enterprise on Windows

For our example, we are using Qlik Sense Enterprise on Windows and the installed Qlik Web Connector for Office 365 Sharepoint. For more information on the Office 365 Sharepoint Qlik Web Connectors and for installation instructions, see: Office 365 Sharepoint and Installation Web Connectors.

Step by Step Instructions (as seen in the video)

Links provided in these examples are example links, not real links.

Using a supported web browser, open up the Qlik Web Connectors Console
YourWebConnectorServer:5555/web
Locate and click the Qlik Office 365 Sharepoint Connector
Select the CanAuthenticate table in the available Tables list on the left.
Click Parameters
Copy your Sharepoint base URL (where the file is located) into the Base URL field
https://<yourO365TenantID>.sharepoint.com
Click Authenticate
This will open a new browser window with your Authentication Code. Copy the code to clipboard.
Return to the previous browser window and paste the code into the Authentication Code field. Click Save.
Now click Save Inputs & Run Table
A confirmation will be shown in the Data Preview tab reading true in the authenticated row.
Locate the ListFolders table in the available Tables list on the left.
Click Parameters
Provide the Base URL and Sub Site Path
- Base URL: https://<yourO365TenantID>.sharepoint.com
- Sub site Path: /sites/<yoursubsiteDirectoryPath>
Click Save Inputs and Run Table
In the now available list, locate the directory your file is located in.
Copy the path and paste it into the Folder field.
/sites/<yoursubsiteDirectoryPath>/Shared Documents/Functional Area - Development
Repeat this step if needed to continue listing nested folders until you locate the folder storing the file.
Click Save Inputs and Run Table
Copy the folder ServerRelativeUrl
Locate the ListFiles table in the available Tables list on the left. This will allow us to identify the ID of the Excel file.
Click Parameters
Paste the ServerRelativeUrl into the Folder field
Click Save Inputs and Run Table
The Data Preview tab will now show us the UniqueId of the xslx document you are interested in. In our example: cf079b74-1bc8-4a5a-971d-07321081a6ac
Copy the ID
Go back and choose the GetFile table
The Sub Site path should be the subsite that you have previously used
Paste the UniqueId into the UniqueId For File field.
Click Save Inputs and Run Table
The Connector will now attempt to preview the data
Click the Qlik Sense (Standard Mode) tab
Copy the URL provided:
http://localhost:5555/data?connectorID=Office365Connector&table=GetFile&subSite=%2fsites%2f<yoursubsiteDirectoryPath>&fileId=cf079b74-1bc8-4a5a-971d-07321081a6ac&appID=
Go to your app in Qlik Sense
Create a connection to a web file using that URL previously copied.
Reload the data to your Sense app

Re: How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

qs123 — Wed, 09 Dec 2020 16:53:20 GMT

Hi, Thank you for the detailed post. The connector was working fine for us but it broke few months ago. Now we have upgrade Qlik to September 2020 and the webconnector to the latest release as well.

We are trying to access an excel for which the user already able to access on share point. The authentication is working fine

but it throws an error getting sub-site

What could be the reason for this?

Thank you

Re: How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

gkkandukuri — Thu, 18 Mar 2021 16:50:59 GMT

hi,

we are using Feb 2020 and when trying to authenticate, it is opening a new tab and asking to enter login and password. which after entering it is asking again to re enter.

Please advise

Re: How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

Francisco_Fernandez — Mon, 22 Mar 2021 08:40:14 GMT

Hello @qs123 ,

Usually, a "no rows returns" means that probably the resource that you are trying to access. I would suggest verifying on SharePoint if that subsite is accessible.

Hello @gkkandukuri ,

Please note Feb2020 version does not exist in QWC, I would suggest using December 2020 version and check if the issue persists.

As a side note, I would recommend requesting assistance in our community (creating a post) to make sure that your questions get answered, our active users and engineers are constantly monitoring these boards.

For this specific topic, the Qlik Connector forum is the best-suited location to get your answers soon!

Best regards,

Francisco

Re: How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

Ken_T — Fri, 13 Aug 2021 19:42:35 GMT

is there a way to get sharepoint metadata about the excel from a qlik connection, such as the sharepoint metadata "Created By" and "Modified By" which show up in sharepoint for the files which are viewable in sharepoint for the files?

Re: How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

DavidFosterVF — Wed, 15 Jun 2022 09:52:12 GMT

is there a way to get multiple files (e.g. all spreadsheets in a folder)?

Re: How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

Sonja_Bauernfeind — Wed, 15 Jun 2022 13:14:13 GMT

Hello @DavidFosterVF

Is this what you are looking for? Ways to access your data in Office 365 Sharepoint

If it is not, I would recommend posting about your query in our active community forum: Connectivity & Data Prep.

All the best,
Sonja

Re: How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

Ken_T — Wed, 29 Jun 2022 13:32:45 GMT

@DavidFosterVF In Qlik Cloud, it works like that.... In SaaS, you can use one SharePoint connection for all the files. In Enterprise, I do not think that functionality is available. You have to create a connection for each file in Enterprise. based on my testing!

I am not sure if Qlik is planning to make Enterprise work like SaaS for this, but --- it is a great improved functionality that is available in SaaS right now.

Re: How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

apurvaxjha — Thu, 07 Jul 2022 06:50:46 GMT

@Sonja_Bauernfeind thanks for elaborating in steps. its helpful. I've a question though.

Does the Qlik Service/System account require to have access on the SharePoint Online? We can't give sys account permission on SPO due to compliance reasons.

Can you please guide what can be done in this case?

Thanks

Apurva

Re: How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

Sonja_Bauernfeind — Thu, 07 Jul 2022 07:15:15 GMT

Hello @apurvaxjha

See Ways to access your data. Valid SharePoint account credentials are required, not the Qlik Service account.

Ways to access your data

You need to authenticate the connection to access your Office 365 SharePoint data .

Do the following:

Enter the SharePoint base URL that you want to connect to, and click Authenticate.

Log in using your SharePoint account credentials.

Retrieve the authentication token and enter it into the connector.

Your credentials are stored in the Qlik Web Connectors.

Re: How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

lorenwood — Tue, 01 Nov 2022 12:47:52 GMT

Hello has anyone been able to connect Sharepoint O 365 to Qlikview (not Qlik Sense) to read an excel file? We are migrating to cloud and not sure how to accomplish. I am reading we need an Office 365 Connector?

Re: How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

marcus_sommer — Tue, 01 Nov 2022 12:58:03 GMT

There is no difference between QlikView and Qlik Sense in regard to the connectors.

Re: How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

Ken_T — Tue, 01 Nov 2022 13:14:21 GMT

@lorenwood it is very easy to do in qlik cloud. it is a connection type available when you are editing your app. the connection prompts you for the sharepoint credentials and these are stored in a token you copy and paste into the connection. the connection will work so long as the user name and password in the token are still valid (if the account gets a new password, you need a new token - for now) then it behaves in Qlik cloud much like a folder connection - and you can access all the files there via one connection.

Re: How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

lorenwood — Tue, 01 Nov 2022 14:37:23 GMT

@Ken_T We have Qlikview On Premise not the cloud version

Re: How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

lorenwood — Tue, 01 Nov 2022 14:38:17 GMT

@marcus_sommer Which connector do we use? Is it the Qlikview web connector?

Re: How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

Ken_T — Tue, 01 Nov 2022 14:52:06 GMT

WHEN you move to the cloud, for this scenario - you will use the Office 365 Sharepoint connection type.

in the list, under the O's

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Helpsites/Connectors_SaaS.htm

this post is about using the the QLIK SENSE web connectors, so we are getting a little off topic 🙂 but it is related. The Qlik Sense Cloud connector for this is much easier to use that the Enterprise one. If you are migrating from QlikVIew to the Qlik Sense Cloud, Qlik can help you with that but you will need to reach out to them directly to discuss that and see if it will involve $

Re: How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

marcus_sommer — Tue, 01 Nov 2022 15:25:43 GMT

The connectors are independent from the tool which use them - just start by the beginning of this blog and follow the instructions (also those within the links) how to install + config the connector and then using it to load data.

Re: How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

AronC — Tue, 08 Nov 2022 11:53:50 GMT

@Ken_T The sharepoint-connector in Qlik Saas, must be renewed every 90 days, which is really enoying for us. I guess this conncetor also needs to be renewed every 90 days?
If it doesn't need to be renewed every 90 days, is it possible to implement this solution you posted also in Qlik Saas?

Regards,

Aron

Re: How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

Alanoud — Thu, 22 Jun 2023 11:48:48 GMT

Dear Sonja

This is really what I need it's working with me.

But I have inquire, if there is any way to connect with the folder

and use multi files inside it, not just one file ?

Thank you,

Re: How to load a file from SharePoint 365 using Qlik Using Qlik Web Connectors

Sonja_Bauernfeind — Thu, 22 Jun 2023 12:22:16 GMT

Hello @Alanoud

I recommend posting about this specific use case in our Qlik Connectivity and Data Prep forum.

All the best,
Sonja