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

[Sonja_Bauernfeind]

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. 

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

[marcus_sommer]

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

[Ken_T]

@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.

[lorenwood]

@Ken_T  We have Qlikview On Premise not the cloud version

[lorenwood]

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

[Ken_T]

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.ht...

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  $

[marcus_sommer]

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.

[AronC]

@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

[Alanoud]

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,

[Sonja_Bauernfeind]

Hello @Alanoud 

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

All the best,
Sonja