How to download files hosted on Sharepoint 365 with Qlik Web Connectors

[Sonja_Bauernfeind]

The Qlik Office 365 Sharepoint Connector (Qlik Web Connectors) provides the query "GetFile", which can fetch binary content of files hosted on Sharepoint but cannot save the file to disk.

This option is sufficient for data files that Qlik engine can read directly (such as Excel, csv, text, xml, etc.). However, this doesn't work with data files that requires a data connection such as Microsoft Access database (.mdb, .accdb). MS Access ODBC / OLE DB connection is unable to load binary content from an URL.

The Qlik Office 365 Sharepoint Connector does not come bundled with the default Web Connectors available for a Qlik Sense Enterprise installation or for Qlik Sense for Business. The full Qlik Web Connector package needs to be downloaded, see Installing Qlik Web Connectors for details.

 

Environment:

Qlik Web Connectors 

Resolution:

R&D is working on improvement request QCWP-2225  to add a DownloadFile/SaveToDisk query for Qlik Office 365 Sharepoint Connector (equivalent to "DownloadFile" of OneDrive Connector)

While waiting for the improvement,  a work-around solution would be using a vbscript to handle the download part as follows:

1. Use GetFile query in Qlik Office Sharepoint 365 Connector to fetch the binary content
2. Copy the generated link http://localhost:5555/data?connectorID=Office365Connector&table=GetFile&.
3. Create a vbscript following this post: https://stackoverflow.com/questions/2973136/download-a-file-with-vbs. Replace the link with the URL in (2) and savetofile with a preferred path/filename
4. In QlikView/ Qlik Sense script, use EXECUTE command (https://help.qlik.com/en-US/sense/September2018/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptReg...) to execute the vbscript and download the mdb file to a local path
5. Load the mdb file using ODBC/ OLE DB connection

 

You can of course use the vbscript to download the file directly from Sharepoint. However, Qlik Web Connectors should save some effort on the authentication phase.