topic Re: For Each File in Filelist - FTP load is not working? in QlikView

For Each File in Filelist - FTP load is not working?

Mon, 26 Jan 2026 18:19:17 GMT

Dear Community,

I am trying to load the .xlsx file directly from ftp server,

and i would like to use "for each" function to select the latest file in that folder,

but this function is not working, anyone could help?

script:

For each File in filelist ('[ftp://testftpname:Password@ftpaddress.com/test_*.xlsx]');Password@ftpaddress.com/test_*.xlsx]');

PS: It is working fine when i am using load from [ftp://testftpname:Password@ftpaddress.com/test_*.xlsx Password@ftpaddress.com/test_*.xlsx]

Thanks and best regards,

Chanel

Re: For Each File in Filelist - FTP load is not working?

Colin-Albert — Wed, 22 Jan 2014 20:40:59 GMT

To load a series of xlsx files you do not need to use a for..each loop,

Just load the data from abc*.xlsx and QlikView will load and concatenate the data from each file automatically provided the data structure is the same in every spreadsheet.

Re: For Each File in Filelist - FTP load is not working?

Fri, 24 Jan 2014 08:18:22 GMT

Thanks for your reply. I wan to use "for each" function is because i need to load all file in the folder and get the latest file.

Re: For Each File in Filelist - FTP load is not working?

Colin-Albert — Fri, 24 Jan 2014 08:42:14 GMT

Can you set a variable to the filelist expression, and see what the variable contains.

Let vFileList = filelist(...)

Re: For Each File in Filelist - FTP load is not working?

jonathandienst — Fri, 24 Jan 2014 08:47:09 GMT

Chanel

If you include a FileName() or FileBaseName() or FileTime() in your load, you will be able to identify which records come from which source file and/or when that file was last updated. Perhaps this will solve your problem.

LOAD .....

FileBaseName() As Source,

....

FROM [ftp:...test_*.xlsx]

HTH

Jonathan

Re: For Each File in Filelist - FTP load is not working?

marcus_sommer — Fri, 24 Jan 2014 09:02:09 GMT

Loads per FTP couldn't have wildcards in path or filename. Also functions like filetime() and filelist() won't be supported from ftp-protocoll. But you could generate a full-path in a variable per algorithm or per in-listing. Alternatively you could use a triggered batch-file (execute statement) with e.g. mget --include "*.csv".

- Marcus

Re: Re: For Each File in Filelist - FTP load is not working?

Mon, 27 Jan 2014 07:00:39 GMT

Dear Colin Stuart

Filelist() looks like not a default function as it is shown in red color, i tried with FIlebaseName(), FileName() but neither is working.

Thanks,

Chanel

Re: For Each File in Filelist - FTP load is not working?

marcus_sommer — Mon, 27 Jan 2014 07:46:03 GMT

How above mentioned the ftp-protocoll doesn't support any file-functions or wildcards. Also I don't believe that your approach to return a proper filelist for a for each loop will generally work - I haven't seen it yet.

- Marcus

Re: For Each File in Filelist - FTP load is not working?

Anonymous — Mon, 24 Feb 2014 19:23:02 GMT

Hi Marcus,

I understand ftp cannot support wildcards, but i want to load all the files from ftp, which are going to be generated 1 file every day..and these are automatically deleted for 15 days; how can I load these dynamic files from ftp into qlikview ?

Re: For Each File in Filelist - FTP load is not working?

Anonymous — Mon, 24 Feb 2014 19:25:00 GMT

Hi Chanel Choo,

Did u able to load all files from FTP? if yes, could you please share sample code!!

Thanks,

Praveena

Re: For Each File in Filelist - FTP load is not working?

marcus_sommer — Tue, 25 Feb 2014 08:01:23 GMT

It will depend from the names of your files. If the names contain the date you could try like this:

for i = 1 to 14

vFileName = FTPServer & Path & File_Prefix & date(today() - i, 'YYYY/MM/DD') & '.csv';

table:

Load * From $(vFileName) (txt, utf8, embedded labels, delimiter is '\t', msq);

- Marcus

Re: For Each File in Filelist - FTP load is not working?

Tue, 25 Feb 2014 08:17:26 GMT

Hi Praveena,

Sorry that I don't have the sample code as I don't load directly from FTP.

I will load data to database with ETL and QV will access to database.

Thanks,

Chanel

Re: For Each File in Filelist - FTP load is not working?

Anonymous — Tue, 25 Feb 2014 20:57:24 GMT

hey Marcus, thanks for the reply,

how can i add file checker to it, they are coming in different folders? is it like i have to create loop for every folder/ any other way?

Re: For Each File in Filelist - FTP load is not working?

marcus_sommer — Wed, 26 Feb 2014 08:12:00 GMT

Unfortunately wildcards and file-funtions aren't supported through the ftp-protocoll - you could try it with complex loops but ther error handling will be enourmous. I suggest you rather to use triggered batch-file (execute statement) with e.g. mget --include "*.csv" to transfer all your files in a local folder and make your checks there.

- Marcus

Re: For Each File in Filelist - FTP load is not working?

pandiarajan — Wed, 02 Apr 2014 06:18:09 GMT

Dear Marcus,

Your above Scripts are working fine. but my problem is i have FTP file name after date format some extenstions are available. how can i solve this situation

Ex : My File Name like this sales_20130220_1034.PSV ; sales_20130221.5444.psv ; sales_20130222.54344.psv

actually my requirement is audit FTP files status. so i cant transfer those file to my local folder.

Re: For Each File in Filelist - FTP load is not working?

marcus_sommer — Wed, 02 Apr 2014 08:01:56 GMT

You need always some logic to create a full-path. If this isn't possible or to complex you must transfer all files from ftp to a local or network storage and could then make normal loads with wildcards and so on. For this you need (a triggered) batch-file (per execute statement) with a statement like this:

open YOURSERVER

USER

PASSWORD

lcd "D:\YOURTARGETPATH"

mget --include "*.csv"

bye

exit

Type "ftp" and "batch" in google and you will find many informations and examples.

- Marcus

Re: For Each File in Filelist - FTP load is not working?

Sat, 23 Aug 2014 12:57:44 GMT

Use this script I believe it will help you\

////////////Connectig only Directory listing through WebFiles Button,
///////////then split Directory listing into 4 Section Date = F1, Time = F2, Type of File/Directory = F3, Name of File or Directory = F4
LOADER:
LOAD F1,
F2,
F3,
F4
FROM
[ftp://username: pass%40word@kolkataivf.net]
(html, codepage is 1252, embedded labels, table is @1, filters(
ColSplit(1, IntArray(11, 24, 34))
)) Where right(F4,4)='xlsx';

let vRow = NoOfRows('LOADER'); ////// Checking No of Rows for xlsx files

For i = 0 to $(vRow)-1
let vFile = peek('F4',-1+$(i),'LOADER'); ////// Remember if All file structures are same then you can execute this script and Last Row started with ‘-1’

LOAD COMPANY,
CEO_NAME,
ADDRESS1,
CITY,
PIN,
EMAIL
FROM
[ftp://username: pass%40word@kolkataivf.net/$(vFile)] /////Now Concatenating File with Connection Bec’z for each file transfer FTP require new connection
(ooxml, embedded labels, table is Sheet1);

Next i

Re: For Each File in Filelist - FTP load is not working?

Mon, 09 Feb 2015 17:45:18 GMT

Hi Chanel,Did you tried my recommended script

Please reply back

Re: For Each File in Filelist - FTP load is not working?

marsman82 — Tue, 07 Apr 2015 12:08:28 GMT

It works like a charm!

Re: For Each File in Filelist - FTP load is not working?

qlikhalmar — Tue, 28 Jul 2015 13:28:40 GMT

Hi Amitava,

Nice script, but I have a folder on my FTP location and when i add the foldername to the location ([ftp://username: pass%40word@kolkataivf.net/foldername], i get an error in the script.

QlikView cannot read the HTML table.

Do you know what the trick is to read the filelist from the folder?

Thanks!

Halmar