Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Refreshing File-based Data and the New Dropbox Connector - Qlik Sense Cloud Business


drop.pngGood day Qlik Community! In this article / video - I'm excited to present to you a preview of the new Dropbox connector that comes with Qlik Sense Cloud Business and Qlik Sense Enterprise, available later this month. In short, Qlik Sense can now securely access Dropbox and pull data from Qlik Sense supported file sources, directly from the cloud share. The connector itself, is fairly simple to use. It's just like accessing a local folder structure. However, it gives you another option for storing data files for use with your Qlik Sense apps. It even opens some new techniques for refreshing file-based used by those apps. So, instead of storing data files in my Qlik Sense Cloud Workspace, I can choose to store them in Dropbox. With this approach I can automate a data file upload process when new data arrives..... add that to Qlik Sense Cloud Business and its Schedule Data Refresh capability, and you can completely automate the data upload and Qlik Sense app refresh process!

Want to learn more? Take a look at this brief video below to see how I used the Qlik Sense Dropbox connector and common, freely available Windows tools to make this happen.

NOTE: The approach I take in the video simulates data being written out / updated to disk and then transferred to Dropbox on an automated schedule using a 3rd party scheduler (Windows Task Scheduler). It is completely up to you how you would like to achieve this. For example, you could have a database process export files directly to the Dropbox folder, or even use an ETL tool workflow and its scheduler to write out data to the Dropbox share.

Have more ideas on how this can be achieved? We want to hear from you, so share them in the comments below.

Regards,

Michael Tarallo (@mtarallo) | Twitter

Qlik

Qlik Sense Cloud Business - Dropbox Connector - Refreshing file-based data

NOTE: To increase resolution or size of the video, select the YouTube logo at the bottom right of the player. You will be brought directly to YouTube where you can increase the resolution and size of the player window. Look for the 'settings' gears icon in the lower right of the player once at YouTube

Can't see the YouTube video?

Download it here to watch on your computer or mobile device?

12 Comments
Employee
Employee

It was possible to achieve this using the REST connector and some hard work configurating the DB API and connections.

Great to have this option now

Employee
Employee

Hi Clever - correct, earlier this year you may recall I created a simple POC with the REST Connector and documented it as a discussion thread here: Update your Qlik Sense Cloud Business workgroup data files with REST and Dropbox - Proof of Concept - it is soooo much easier with this NEW Dropbox connector - Thanks for your comment.

pedrobergo
New Contributor III

Hi mtarallo

It´s a really good news. Save many of our hours.

Do you know if Qlik will do the same for other services, like OneDrive or GoogleDrive?

Tks

0 Likes
Employee
Employee

Hi Pedro - I have the question out to PM. However we do offer these connectors with our Web Connectors package - just as a reference in case you need more information.

http://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/Setup-deploy/Qlik_Web_C...

Qlik Market  - ‌ you can learn more here:

tucahernandes
New Contributor II

Hello mtarallo

I'm Qlik Cloud Business' subscriber. The dropbox connector option isn't available for me yet. Is it the current situation for all Business' subscribers yet?

0 Likes
Employee
Employee

‌HI Marcelo  I am told it will be available this month so it could be available anytime from now until the end of November.  Thanks  for your question

evizcayaretail
New Contributor

Hi Michael,

I have the same question than an other user:

"I am using Qlik sense Cloud to automatically reload data on a daily basis.

The data I would like to load consist of .csv files from one or more folders, so I don´t only want to load 1 file, but sometimes thousands.

Normally I would just make do this.

  1. Load 
  2.     data1 
  3.     data2 
  4. FROM [lib://location-data/*] 

But it fails when I am trying to do the same with DropBox

  1. LIB CONNECT TO 'Valuereport (qlikcloud_qlikid)'; 
  2. Load 
  3.     data1 
  4.     data2 
  5. FROM [lib://Valuereport (qlikcloud_qlikid_varmekontrol)/folder1\location-data\*] 



And fails when doing it with FTP (Witch I would prefer)

  1. Load 
  2.     data1 
  3.     data2 
  4. FROM [lib://valuereportsa (qlikcloud_qlikid)] 

Thanks,

Ernesto Vizcaya

0 Likes
Employee
Employee

HI Ernesto - thanks for  your question, the reason for this failure is because the connection to dropbox or FTP is not open - let me see if there is some work-around.

0 Likes
evizcayaretail
New Contributor

Thanks Michael,

An in this kind of script neither

FOR EACH file in filelist('lib://qlikcloud_qlikid)/location-data/*')

regards,


Ernesto

0 Likes
Employee
Employee

Hi Ernesto - just got word from the cloud team - multiple file reads in those connectors are not supported. Qlik Sense Enterprise does this support this capability.

0 Likes
Employee
Employee

Hi Ernesto - I'm pleased to inform you that I have been given a work-around from our dev team - this only works in Qlik Sense Cloud Business.

Workaround to be able to load, in cloud, multiple files from a connected Dropbox folder.

Procedure:

  • Open/Create App in Qlik Cloud Business and go to the DLE. (data load editor)
  • Create connection to the Dropbox _Metadata_ connector - it allows you to load the list of files (Table1)
  • Select any of these files from Dropbox connector (Table2)
  • Create a loop to go through the list of files from the Table1 and replace the filename in the Table2 script by the loop variable (that contains filename)

Sample script load script:

//TABLE1

LIB CONNECT TO 'Dropbox_Metadata';

LOAD path_lower as [List.path_lower];

SELECT 

path_lower

FROM List

WITH PROPERTIES (

dropboxFolder='/', recursive='false', includeMediaInfo='false', includeDeleted='false');

LET noRows = NoOfRows('List');

// for i=0 to $(noRows)-1 /

/ loop through every row

for i=0 to 2 

let vFilePath = peek('List.path_lower', $(i), 'List');  

//TABLE2 

LIB CONNECT TO 'Dropbox'; 

LOAD

name,     

surname,     

"group",     

"date",     

"test/read",     

"test/error",     

"test/pravotv",     

"test/time",     

"test/timerest",     

"test/quality",    

"test/tochn",     

"test/efectd" 

FROM [lib://Dropbox$(vFilePath)]  (XmlSimple, table is Result);

next

*please note that you need to create 2 connections - one for `dropbox` (storage) and one for the `dropbox metadata` also keep in mind that it works for the same structured files

*important: Dropbox metadata is available on *Cloud only* as built-in connector.

For Desktop/Enterprise you need to install QWC standalone ( Connectors-> Qlik Web connectors)

let me know how you do

Mike

evizcayaretail
New Contributor

Hi Mike, thanks for your fast and complete response,

I'll check.

regards,

0 Likes