Looping Files on Dropbox / Google Drive

[malradi88]

To Whom it May Concern,

I hope this finds you well. I have been unsuccessfully trying to create direct connections to folders on google drive / dropbox for sometime now 😞 With individual files everything works fine but I cannot seem to figure out how to create a data loop. On dropbox I tried this script:

LIB CONNECT TO 'Dropbox - random@random.org.uk (random-qlik-01_qliksupport)';

set vconcatenate=';';

for each file in filelist('lib://Dropbox - random@random.org.uk (random-qlik-01_qliksupport)/folder/subfolder/Q*.xlsm')

[TABLE]:

LOAD
*
from [$(file)]
(ooxml, embedded labels, header is 1 lines, table is [sheet1]);

set vconcatenate='concatenate';

next file

but nothing loaded (this script works for files directly on my server). For google drive I've tried similar things with the same results. 

I came across this article that seems to offer a solution:

https://www.quickintelligence.co.uk/cloud-file-services-qlik-sense/

I am working with Qlik Sense Enterprise though and can't seem to find a 'meta data' connector. Is this only for Qlik Sense Business? Has anyone come across a solution for loading multiple files from either dropbox or google drive via data loops? Most of the people I work with use the former and latter and having to upload each file separately is too time-consuming/complicated for users. 

Thank you for your support!

Best,

Mohammed

[stevedark · Accepted solution]

Hi,

Hopefully my response on this thread will help with Google Drive:

https://community.qlik.com/t5/Qlik-Connectors-Discussions/Google-Drive-Direct-Folder-Connection/m-p/...

It's similar with DropBox, there is no separate MetaData connector in QlikWeb connectors. All of the API endpoints are in the same connector.

In the same way that my blog post talks about loading from the MetaData connector and then enumerating around the results you can do the same with the List endpoint in QWC. This will give you a list of files and folders.

You can then step through the results of the List query and call one of the other endpoints from there, such as GetRawFileAsBinary or GetRawFileAsText.

As you are injecting bits of code you will probably need to look at the syntax for WITH CONNECTION in order to get that working.

Hope that helps.

Steve

[chrisbrain]

Hi Mohammed,

Wildcard matching (e.g. where you have Q*.xlsm in your example) is not supported in either the Dropbox or Google Drive WebStorage provider connectors - I believe this is mentioned in the help page.

In Qlik Sense Business though you should find both a Dropbox Metadata connector:
https://help.qlik.com/en-US/connectors/Subsystems/Integrated_Web_Connectors_help/Content/Connectors_...

And a Google Drive & Spreadsheets connector:

https://help.qlik.com/en-US/connectors/Subsystems/Integrated_Web_Connectors_help/Content/Connectors_...

Which contain tables which allow you to list files and then create a load script which loops through the rows in these and load files that way in each iteration.

(UPDATE: And you should be able to use the filelist function to list files in folders as long as you don't use wildcards and so avoid using a 'metadata' variant of the connector).

With QlikView and Qlik Sense on premise you can likely use the Qlik Web Connectors:
https://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/Connectors_QWC/Setup-d...

To achieve similar goals although with these there is a single 'Dropbox' and 'Google Drive & Spreadsheets' connector which should cover both use cases (e.g. listing the files in a table and then loading them into qlikview/qlik sense).

[chrisbrain]

Ah should have checked @stevedark  hadn't already replied while I was writing my response!