Skip to main content

Office 365 Sharepoint

The Qlik Office 365 Sharepoint Web Storage Provider connector lets you connect seamlessly with your remote data files, allowing you to stream data directly into your Qlik Sense app. With the Office 365 Sharepoint connector, select and load data from your Office 365 Sharepoint account just as you would from a local file.

Supported offerings

  • Qlik Sense Business
  • Qlik Sense Enterprise SaaS
  • Qlik Sense Enterprise on Windows
  • Qlik Sense Desktop

Ways to access your data

You need to authenticate the connection to access your Office 365 SharePoint data. You can access the connector through Add data and the Data load editor:

  • From Add data, select Office 365 SharePoint from the list of data connectors.
  • From the Data load editor, click Create new connection and select Office 365 SharePoint.

Do the following:

  1. Enter the SharePoint base URL that you want to connect to.

    You must know any subsites before using this connector, as they will not display in Qlik Sense.

  2. Select a Prompt/consent level:

    • Consent: Choose this option if you are an administrator.

    • None: Choose this option if you are a user and your administrator has already consented on your behalf.

    • select_account: Choose this to select which account to use. This will interrupt single sign-on. It provides a list of all the accounts either in session or any remembered account. Or you can choose a different account entirely.

    These settings may differ depending on the configuration.

  3. Click Authenticate.

  4. Log in using your SharePoint account credentials.

    If the login page does not open, disable your pop-up blocker in your browser.

  5. Retrieve the authentication token and enter it into the connector.

  6. Click Create.

When you are finished setting up your data connection, click Create to save the connection. The connection credentials are verified automatically. From the Data load editor, you can also verify the connection before you create it by clicking Test Connection.

Connector location

The location of the connection depends on how you select data.

  • From Add data, it will be saved under File Locations.

  • From the Data load editor, it will be saved under Data connections with the name that you provided.

Selecting data

When you create a connection with Add data, the data selection step follows immediately. When you create a data connection with the Data load editor, you need to open the data connection before you can select data.

You can select and load data from the following files types:

  • Delimited files: csv, txt, tab, qvo, mem, shv, prn, log
  • Excel files: xls, xlw, xlsx, xlsm
  • HTML files: html, htm, php
  • KML files: kml
  • Fixed record files: fix, dat
  • QlikView data files: qvd
  • QlikView data exchange files: qvx
  • XML files: xml

Do the following:

  1. Navigate to the file path location on your Sharepoint account.

  2. Locate your files by clicking on the folder names. You can navigate backwards by clicking on the previous folder in the path field or by clicking Undo.

  3. You can filter the file types that are shown by using the File type drop-down menu.

  4. Select a file.

  5. Select the data tables and the data fields that you want to load.

    You can rename data fields before loading them into Qlik Sense by clicking on the field name and entering a new name. The new name is mapped by Qlik Sense as an alias to that field name in the database.

Loading data into your Qlik Sense app

Once you have finished selecting data, you can load your data into your Qlik Sense app. How you proceed will depend on whether you load data with Add data or the Data load editor.

Add data

Do the following:

  1. Click Add data. This will open the data in the Associations view of the data manager.

  2. In the associations view, you can continue to add data sources, transform the data, and associate the tables in Data manager.

  3. Click Load data.

Data profiling is enabled by default when you click Add data. Data profiling does the following:

  • Recommends data associations.
  • Auto-qualifies common fields between tables. This adds a unique prefix based on table name.
  • Maps date and time fields to autoCalendar.

Tables are not associated on common field names automatically. You can associate tables in the Associations view.

Managing data associations

Tip note

If you want to load the data directly into your app, click More and then disable data profiling.

When you add data with data profiling disabled, all existing data from data sources will be reloaded when you add the data. Tables will be associated on common field names automatically. Date and time fields will not be created.

Data load editor

Do the following:

  1. Click Insert script once you are finished selecting the data. A load script is inserted into the script editor.

  2. You can continue to edit the script in the script editor or you can click Load data to run the data load script.

Storing data from your Qlik Sense app in Office 365 Sharepoint

Using the Data load editor, you can store table data into your Office 365 Sharepoint repository.

Information noteThe data storage feature is currently only available on Qlik Sense SaaS.

Once you have loaded data into your Qlik Sense app, you can store your table data in Office 365 Sharepoint, by creating a new load script or editing the existing script. Using the SELECT command, specify a list of fields to be included in the upload, and then write a Store command to set the file path in Office 365 Sharepoint. Click Load data to run the data load script, and Qlik Sense will complete the upload process.

Data can be stored in the following formats:

  • qvd
  • txt

Syntax: Storing data in Office 365 Sharepoint

STORE SomeTable into 'lib://Office365 Sharepoint/Some/Path/storedFile.txt' (txt);

Office365 Sharepoint in this example is the name of the Sharepoint connection.

See the Store syntax for more detailed scripting instructions.

Limitations and known issues

When authoring load scripts for use with the Office 365 Sharepoint Web Storage Provider Connector, there are specific limitations to be aware of:

  • You cannot store or upload files to a folder that does not exist.
  • You cannot load or store a file that does not contain a filename extension.
  • Document library names in the tree view may differ from the UI name on the Sharepoint site.

  • Sharepoint Views are not supported.

Reference - SharePoint REST API documentation

You can refer to the SharePoint REST API documentation to learn more about the requirements and restriction imposed by the SharePoint REST API.

SharePoint REST service