Hello
We had to switch from Qlik Sense Desktop to the Cloud Version (Qlik Sense Business).
On the old App our data (multiple, identically structured Excel-Files stored in one folder) got loaded by a simple folder path connection: e.g. G:QLIK_DATA/Shipments/*.xlsx (every week a new Excel-File got added to the folder).
Now we cannot directly get the data from "on-premise" in the new cloud version of Qlik (which actually sucks a bit). So we put all our data on a OneDrive Folder.
But now I have the issue that the * Command in the FROM path (meaning loading all Files from a specific folder) does not work anymore.
Can anybody tell me how I am able to load multiple excel files from a OneDrive folder? Do I have to use some sort of loop?
PS: All Excel-Files share the same table name.
Thank you very much for your insight!
Hi,
This blog post describes how to deal with it for a number of other Cloud providers:
https://www.quickintelligence.co.uk/cloud-file-services-qlik-sense/
The OneDrive connector was not there when I wrote this (must get around to updating it sometime), but does now exist. Like the other connectors there are two separate connectors, a MetaData one and a File one. You need to get the list from one and then loop around getting the detail from the other.
I think that the code you will need for OneDrive will be something like this:
LIB CONNECT TO 'OneDrive_Metadata';
tmpAllFileSelections:
LOAD
*
;
SELECT
id,
name,
createdDateTime,
webUrl
FROM Items
WITH PROPERTIES (
driveId='',
folderId='',
path='/Your/Path',
maxResults=''
);
for iFile = 0 to NoOfRows('tmpAllFileSelections') -1
let vFile = peek('name', iFile, 'tmpAllFileSelections');
SalesFiles:
LOAD
*,
'$(vFile)' As FileName
FROM [lib://OneDriveData/Your/Path/$(vFile)]
(txt, codepage is 28591, no labels, delimiter is ',', msq)
;
Next
DROP TABLE tmpAllFileSelections;
Another approach is the new Qlik DataTransfer tool. This is not something I have tried yet, but it looks pretty cool:
https://www.qlik.com/us/products/qlik-data-transfer
Good luck!
Steve
Hi,
This blog post describes how to deal with it for a number of other Cloud providers:
https://www.quickintelligence.co.uk/cloud-file-services-qlik-sense/
The OneDrive connector was not there when I wrote this (must get around to updating it sometime), but does now exist. Like the other connectors there are two separate connectors, a MetaData one and a File one. You need to get the list from one and then loop around getting the detail from the other.
I think that the code you will need for OneDrive will be something like this:
LIB CONNECT TO 'OneDrive_Metadata';
tmpAllFileSelections:
LOAD
*
;
SELECT
id,
name,
createdDateTime,
webUrl
FROM Items
WITH PROPERTIES (
driveId='',
folderId='',
path='/Your/Path',
maxResults=''
);
for iFile = 0 to NoOfRows('tmpAllFileSelections') -1
let vFile = peek('name', iFile, 'tmpAllFileSelections');
SalesFiles:
LOAD
*,
'$(vFile)' As FileName
FROM [lib://OneDriveData/Your/Path/$(vFile)]
(txt, codepage is 28591, no labels, delimiter is ',', msq)
;
Next
DROP TABLE tmpAllFileSelections;
Another approach is the new Qlik DataTransfer tool. This is not something I have tried yet, but it looks pretty cool:
https://www.qlik.com/us/products/qlik-data-transfer
Good luck!
Steve
Steve does some amazing work - thanks for this one...
There is a great youtube walkthrough vid to go with it - https://www.youtube.com/watch?v=_ufHf9Wm4XA
Thanks @AdyWorrall !