How to load multiple Excel Files from OneDrive Folder to Qlik Sense Business (Cloud)
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?
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: