topic Re: Load all files from folder in QlikView

Load all files from folder

viveksingh — Wed, 25 Nov 2020 16:16:04 GMT

HI All,

i have a folder path where every day file will be placed. Lets say one file is placed with year_month_date as file name. If one file is placedin the folder it should load and for tomorrow one more file will be placed with date as tomorrow. tgen it should load yesterday’s file and today’s file. As the field name and field count is same, it will concatinate. Like this we will get one file every day So All files should load.

PLease help on this

‌

Re: Load all files from folder

andrey_krylov — Fri, 28 Sep 2018 03:29:22 GMT

Hi. If all these files have the same structure then you can use a wildcard

LOAD * from [PathToFolder\*.xls](ooxml, embedded labels, table is Sheet1);

or like this

[Table]: LOAD * InLine [TempField];

for each FoundFile in filelist( 'PathToFolder\*.xls')

If SomeCondition Then

Concatenate(Table) LOAD * from [$(FoundFile)] (ooxml, embedded labels, table is Sheet1);

EndIf

next FoundFile

Re: Load all files from folder

viveksingh — Fri, 28 Sep 2018 03:49:20 GMT

‌Thanks for the reply Andrew.

All files are text files Which comes on daily basis.

lets say for today- 28/9/2018. Below is the file name

textfile_2018-sept-28.txt

for tomorrow we will get one more file. Which mean in the folder we have two files. One is yesterday file and other is today’s fil. We have to load both.

list of files available in the folder for tomorrow:

textfile_2018-sept-28.txt

textfile_2018-sept-29.txt

Re: Load all files from folder

andrey_krylov — Fri, 28 Sep 2018 04:04:36 GMT

Try to put asterisk to the path

LOAD * FROM [PathToFolder\textfile_2018-sept-*.txt] (txt, codepage is 1251, embedded labels, delimiter is ' ', msq);

Re: Load all files from folder

viveksingh — Fri, 28 Sep 2018 05:16:20 GMT

I tried the sane and working fine. But my concern is going forward the year will change like 2019 and month also changes. so for that i have created two variables one is for current year and other is for current month. How to add this variable in from path.

Let v_current_year= year(today());

Ler v_current_month=Num(month(today()));

Re: Load all files from folder

andrey_krylov — Fri, 28 Sep 2018 05:51:12 GMT

If you want to load all files to the only table then slightly change the path

LOAD * FROM [PathToFolder\textfile_20*-*-*.txt] (txt, codepage is 1251, embedded labels, delimiter is ' ', msq);

LOAD * FROM [PathToFolder\textfile_$(v_current_year)-$(v_current_month)-*.txt] (txt, codepage is 1251, embedded labels, delimiter is ' ', msq);

but v_current_month in this case is numeric like 1, 2,...12 and files must be renamed as textfile_2018-9-28.txt

Re: Load all files from folder

rajivmeher — Fri, 28 Sep 2018 08:46:21 GMT

Hi Vivek

Just want some clarification, do you want to load every files from the folder? Or you want to load only files for the current month or only files for last two days?

If you want to load all files than you can use the following:

LOAD *

FROM [FolderPath\textfile_*.txt](txt, codepage is 1251, embedded labels, delimiter is ' ', msq);

Regards

Rajiv.