topic Re: How to load multiple Excel Files from OneDrive Folder to Qlik Sense Business (Cloud) in Connectivity & Data Prep

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

qlik_user2 — Thu, 23 Dec 2021 15:07:58 GMT

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!

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

stevedark — Fri, 04 Sep 2020 15:23:38 GMT

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

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

AdyWorrall — Thu, 09 Feb 2023 09:42:26 GMT

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

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

stevedark — Thu, 09 Feb 2023 10:12:24 GMT

Thanks @AdyWorrall !