Load data from several google drive folders

Anna12 — Mon, 17 Jan 2022 12:55:03 GMT

Dear all,

hope you are doing fine!

I have an issue with loading data from google drive. I want to load several files from different folders on google drive. I found several options to load data from dropbox. The skrip is like this:

LIB CONNECT TO 'Dropbox_NAME - Mail';

Ordner:
LOAD tag as [List.tag],
name as [List.name];
SELECT tag,
name
FROM List
WITH PROPERTIES (
dropboxFolder='/Name',
recursive='false',
includeDeleted='false'
)
;

x:
Load [List.name]
Resident Ordner
Order By [List.name] asc;

DROP TABLE Folder;

LIB CONNECT TO (dropbox);

for iFolder = 0 to NoOfRows('x') - 1
let vFolder = peek('List.name', iFolder, 'x');

Sales:
LOAD *
FROM [lib://dropbox -Mail/Folder/$(vFolder)/sales_x.csv] (txt, codepage is 28591, embedded labels, delimiter is ';', msq);

This works for Dropbox, but I dont know how to do this with google drive. What do i put instead of " dropboxFolder='/Name'," ?

If i just change the source, it tells me "Cannot open file".

I appreciate your help!!!

Best

Anna

Re: Load data from several google drive folders

Bjorn_Wedbratt — Wed, 19 Jan 2022 10:58:44 GMT

Hi @Anna12 ,

You can achieve the above using a combination of Google Drive and Spreadsheets connector and the Google Drive connector.

Basically you can use Google Drive and Spreadsheets connector to get get the folders and folder id:s from Google Drive:

SELECT title, id FROM ListFiles WITH PROPERTIES ( driveId='', query='mimeType = ''application/vnd.google-apps.folder''' );

After identifying the folder id:s, you can loop through the selected folder(s) and filter on specific file types. As an example, let's filter out csv files only:

SELECT title, id, mimeType FROM ListFiles WITH PROPERTIES ( driveId='', query='"$(folder)" in parents and mimeType = ''text/csv''' );

Now the Google Drive connector can be used to read in the files (csv:s in this example) located in each folder specified

for Each file_name in FieldValueList('file.title'); Qualify *; // making sure field names are unique to aviod synthetic keys LOAD * FROM [lib://Google_Drive/$(folder)/$(file_name)] (txt, codepage is 28591, embedded labels, delimiter is ',', msq); next

Attached is a small example that will make it a bit clearer.

You need to attach the Google connectors to your Google account and then run the script once to get the table id:s in the table. Then manually copy/paste the folders to the variable defined in the script (vFolderIds).

Hope this helps!

Best

Bjorn

topic Load data from several google drive folders in App Development

Load data from several google drive folders

Re: Load data from several google drive folders