Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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