Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to load all the files (google sheet) from a folder from a drive on Qlik Sense but i got the following error "noFileSize". Is it a syntax issue ?
Here the following script :
LIB CONNECT TO 'GDR_1V_WORKLOAD_MANAGEMENT';
SELECT title,
id,
mimeType
FROM ListFiles
WITH PROPERTIES (
driveId='driveId',
query='"folderId" in parents and mimeType = "application/vnd.google-apps.spreadsheet"'
);
for Each file_name in FieldValueList('title');
Qualify *; // making sure field names are unique to aviod synthetic keys
LOAD
*
FROM [lib://GDR_1V_WORKLOAD_MANAGEMENT/folderId/$(file_name)];
next;
I don't know the mechanism to load Google spreadsheets but I'm surprised that your LOAD instruction doesn't specify the format (like (qvd),(ooxml)...) , by default this is expected to be a text file.
Issue with Meta data retrieval
LIB CONNECT TO 'GDR_1V_WORKLOAD_MANAGEMENT';
// Retrieve file metadata
FilesMetadata:
SELECT title, id, mimeType
FROM ListFiles
WITH PROPERTIES (
driveId='driveId',
query='"folderId" in parents and mimeType = "application/vnd.google-apps.spreadsheet"'
);
// Load data from each file
for Each file_row in FilesMetadata
Qualify *; // making sure field names are unique to avoid synthetic keys
LOAD
*
FROM [lib://GDR_1V_WORKLOAD_MANAGEMENT/folderId/$(file_row.id)];
next;
Are you using QlikSense 'on-premises' if so what version?
I posted your question to ChatGPT, it returned this code:
// Example script
GoogleSheetsTable:
LOAD *
FROM [lib://GoogleSheetsConnector/YourGoogleSheetID]
(ooxml, embedded labels, table is Sheet1);
The $(file_row.id) is empty in the console, but the FilesMetaData request retrieves 2 lines, so i don't understand why it's empty.
Maybe i need to specify the format of the googlesheet (which is ... ?) like the answer before ?
Try with this small change to your original
for Each file_name in FieldValueList('title')
Qualify *; // making sure field names are unique to avoid synthetic keys
LOAD
*
FROM [lib://GDR_1V_WORKLOAD_MANAGEMENT/folderId/$(file_name)]
(ooxml, no labels, header is 1 lines, table is [Sheet1]);
next;
From what I've just seen, this seems to be in QVX format when using the connector for Google sheets.
I tried to added (ooxml, no labels, header is 1 lines, table is [Sheet1]); (or using qvx) to my first request but it's not working. This time the name of the $(file_name) appears, but still, i've got the noFileSize error.
I also tried to change Sheet1 by the name of the sheet of my spreadsheet, and this is the same result...
Currently, my google sheet has 1 sheet but it will contain more in the future so the (table is [Sheet1]) will be porbably useless (even if the current code isn't working...) ?
FROM [lib://GDR_1V_WORKLOAD_MANAGEMENT/folderId/$(file_name)];
the GDR_1V_WORKLOAD_MANAGEMENT is my connector define by
LIB CONNECT TO 'GDR_1V_WORKLOAD_MANAGEMENT'
Is it the good syntax ? Obviously i'm missing something but i don't what.
Not sure that this is the proper method in fact if we compared to this video https://help.qlik.com/en-US/video/RalaPTmYBCM
I need to use a loop because i don't know how many files will be in the folder of the drive. That's why i'm using this method.
My purpose is to load all the google sheet files in a folder from a drive which is already connected to Qlik Sense.