How to load Excel file from online file storage services (Box, Dropbox, Google Drive, OneDrive)
Qlik Web Connectors allows loading Excel files (xls, xlsx) hosted in online file storage services such as Box, Dropbox, Google Drive and OneDrive directly into QlikView and Qlik Sense without having to save the file to disk first.
Different methods need to be applied to fetch files depending on what edition of Qlik Sense is used.
With Qlik Sense SaaS Editions (Qlik Sense Enterprise Business and Qlik Sense Enterprise on Cloud Services), Qlik offers a number of built in connectors. See Built-in Qlik Web Connectorsfor a list of available connectors.
Note that the in-built Web Connectors are available are:
Unavailable at the moment are:
Sharepoint getfile and download file
To connect to, for example an excel file, stored on Google Drive:
Open a Qlik Sense App
Navigate to Add data
Choose Google Drive
Click Authenticate and follow the Google Drive authentication steps
Copy the authentication code and paste it into the provided text bar
You can now choose supported files.
More information:Managing data sources in spaces. You can add data files and data connections directly in shared and personal spaces. This enables data sources to be added outside of apps for use by other space members.
With Qlik Sense Enterprise on Windows:
We use the query GetRawFileAsBinary of the appropriate connector in Qlik Web Connectors.
Once the binary content is sucessfully loaded in Qlik Web Connectors web console, you can create a webfile connection in QlikView or Qlik Sense to load data from Qlik Web Connectors.
Choose the connector (for this test we are using the Qlik Box Connector)
Select the query ListFilesAndFolders to identify the ID of the Excel file you want to load
Run the query with the Folder ID parameter left blank
This returns all files and folders in the root folder of the Box account
Copy the ID of the folder which the Excel file is located in and paste it into the Folder ID parameter of the ListFilesAndFolders query
Run the query again
This will list all the files in the subfolder
Copy the ID of the file we want to load
Switch to the query GetRawFileAsBinary, choose Parameters and fill in the File ID
Click Save Inputs & Run Table
In our example, no Data Preview is shown.
Obtain the URL to use by switching to the QlikView tab and copying the connection string
Open QlikView and the Script Editor
Select Web Files...
Paste the link previously obtained into the Internet File text box