topic Re: Load all Excel Files in a Directory in App Development

Load all Excel Files in a Directory

EvanBarrick — Mon, 17 Dec 2018 20:53:14 GMT

I have a directory that contains excel files, that is updated upon conclusion of events. I want to load all of the files in the directory into a QVD file. I understood how to do this in Qlikview however I am new to Qlik Sense and am struggling to accomplish this. Any help is appreciated. Current structure of script below:

Directory 'F:\WaitTimes';

For each ExcelFile in filelist ('*.xlsx')
WaitTimesData:
BUFFER (incremental) Load * From $(ExcelFile) (ooxml, embedded labels, table is [WaitTimes]);
Next ExcelFile;

STORE WaitTimesData INTO [F:\DataAgg\WaitTimesAggregate.qvd] (qvd);

Re: Load all Excel Files in a Directory

rwunderlich — Mon, 17 Dec 2018 21:41:18 GMT

Create a folder connection that points to the directory. Then use just the filename portion in the From

For each ExcelFile in filelist ('lib://mydir/*.xlsx')

Let fname = SubField('$(ExcelFile', '\', -1);

LOAD ... From lib://mydir/$(fname)

Even easier may be to forgo the For loop and use wildcards:

WaitTimesData:
BUFFER (incremental) Load * From lib://mydir/*.xlsx (ooxml, embedded labels, table is [WaitTimes]);

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Load all Excel Files in a Directory

EvanBarrick — Mon, 17 Dec 2018 21:49:04 GMT

Thank you for the help. I had the folder created, however when I run the script I get the error of 'Connection not found'

I created the connection and named 'WaitTimesData'

The actual directory path is E:\Data\WaitTimes

Which title do I use in the from portion

Re: Load all Excel Files in a Directory

EvanBarrick — Mon, 17 Dec 2018 21:59:34 GMT

For context, the option you provided that I utilized

WaitTimesData:
BUFFER (incremental) LOAD * FROM lib://WaitTimesData/*.xlsx (ooxml, embedded labels, table is [WaitTimes]);

Re: Load all Excel Files in a Directory

rwunderlich — Tue, 18 Dec 2018 19:29:55 GMT

Start by loading a single file using the select from connection button. Then modify the filename in the generated statement to use "*".

-Rob

Re: Load all Excel Files in a Directory

EvanBarrick — Tue, 18 Dec 2018 19:31:21 GMT

Your original answer worked, I had a small error on my end that took some time to notice. Thanks for the help.