Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
qlik_user2
Contributor
Contributor

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

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!

Labels (3)
1 Reply
stevedark
MVP & Luminary
MVP & Luminary

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