Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
malradi88
Creator II
Creator II

Looping Files on Dropbox / Google Drive

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

 

 

Labels (2)
1 Solution

Accepted Solutions
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

3 Replies
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Partner - Specialist II
Partner - Specialist II

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

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense
chrisbrain
Partner - Specialist II
Partner - Specialist II

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

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense