How to load multiple Excel Files from OneDrive Folder to Qlik Sense Business (Cloud)

[qlik_user2]

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!

[stevedark · Accepted solution]

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

[AdyWorrall]

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

[stevedark]

Thanks @AdyWorrall !