topic Re: Qlik Sense - Load all files from a folder in Connectivity & Data Prep

Qlik Sense - Load all files from a folder

arturomiquelveyrat — Mon, 08 May 2017 17:42:56 GMT

In Qlik View, all files from a folder can be easily loaded using FileList():

for each file in FileList('C:\Users\...\Test\*')

load

$(file) as [File Name],

X as [Field X]

from $(file)

(...;

However, I cannot make FileList work in Qlik Sense. Any advice?

Arturo

Re: Qlik Sense - Load all files from a folder

arturomiquelveyrat — Tue, 09 May 2017 11:23:21 GMT

1. Go to the "Data Loading Editor":

2. Create a new conection to the folder where your files are:

3. Cut and paste the following code:

for each file in filelist('lib://YourConnectionName\*.qvd')

YourTableName:

load

YourField1,

YourField2

from [$(file)](qvd)

next file

Solution adapted from: Read existing files

Re: Qlik Sense - Load all files from a folder

Anonymous — Tue, 09 May 2017 14:44:53 GMT

For loading all files from a folder one could simply use :

load

Field_1,

Field_2

from C:\YourFolder\*.* ;

Re: Qlik Sense - Load all files from a folder

arturomiquelveyrat — Tue, 09 May 2017 14:55:52 GMT

Thanks Bill!

However, I forgot to mention that I need to populate one field with the file name, so your approach is not suitable. I've just edited the question to address this issue.

Re: Qlik Sense - Load all files from a folder

berndjaegle — Fri, 12 Jan 2018 07:39:13 GMT

Thank you Arturo for the example script.

I forgot to put the file type I am loading. The result table showed one record with the XML header.

from [$(file)] (qvd)

BR,

Bernd

Re: Qlik Sense - Load all files from a folder

arturomiquelveyrat — Fri, 12 Jan 2018 08:14:59 GMT

Absolutely! I've edited the answer to include your correction.

Re: Qlik Sense - Load all files from a folder

farolito20 — Thu, 31 May 2018 18:35:17 GMT

Is there any limit of files using this way?

Re: Qlik Sense - Load all files from a folder

arturomiquelveyrat — Tue, 20 Nov 2018 14:10:26 GMT

I don't think so.

Re: Qlik Sense - Load all files from a folder

Zaga_69 — Tue, 05 Nov 2019 17:17:34 GMT

Hi Arturo,

I was following your code and made some adjustments and it works. However I would like to read ALL QVDs stored in the server. This means, I would like to replace 'lib://Stream_0600_QStorage/20_Transform/*.qvd' by 'lib://*.qvd'

It did not work. I want to avoid going folder by folder in the server, I would like to go directly to all QVDs stored in the different folders.

FOR each vFile in FileList('lib://Stream_0600_QStorage/20_Transform/*.qvd')

//variables
LET vFileName = mid('$(vFile)', index('$(vFile)', '/', -1) + 1, 40);
//-----------------------------
QVD_Info:
FIRST 1 LOAD
1 as QVDCount,
NUM(FileSize()/1000000 ,'0') as QVD_Size_MB,
FilePath() as QVD_Path,
FileTime() as QVD_Time_Created,
'$(vFileName)' as QVD_Name
FROM [$(vFile)](qvd);
//----------------------------

NEXT vFile

Any idea?

Many thanks!!!

Re: Qlik Sense - Load all files from a folder

arturomiquelveyrat — Wed, 06 Nov 2019 17:31:57 GMT

I think you'll have to scan through all sub-folders, I'm afraid. Maybe this post helps you: https://community.qlik.com/t5/QlikView-Scripting/Load-files-from-subfolders-in-one-load-script/m-p/1152333/highlight/true#M76446