Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
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

 

 

1 Solution

Accepted Solutions
stevedark
Luminary Alumni
Luminary Alumni

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
Luminary Alumni
Luminary Alumni

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

chrisbrain
Specialist II
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).

chrisbrain
Specialist II
Specialist II

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