topic Re: Looping Files on Dropbox / Google Drive in Connectivity & Data Prep

Looping Files on Dropbox / Google Drive

malradi88 — Thu, 23 Dec 2021 18:53:01 GMT

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

Re: Looping Files on Dropbox / Google Drive

stevedark — Wed, 08 Jul 2020 16:46:41 GMT

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/1726188/highlight/false#M2269

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

Re: Looping Files on Dropbox / Google Drive

chrisbrain — Wed, 08 Jul 2020 16:52:57 GMT

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_QWC_BuiltIn/Integrated%20connectors/Dropbox-Metadata-Connector-integrated.htm

And a Google Drive & Spreadsheets connector:

https://help.qlik.com/en-US/connectors/Subsystems/Integrated_Web_Connectors_help/Content/Connectors_QWC_BuiltIn/Integrated%20connectors/Google-Drive-and-Spreadsheets-Connector-integrated.htm

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-deploy/Qlik_Web_Connectors_package.htm

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).

Re: Looping Files on Dropbox / Google Drive

chrisbrain — Wed, 08 Jul 2020 16:48:18 GMT

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