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

[#1]

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_*.xlsxPassword@ftpaddress.com/test_*.xlsx]

Thanks and best regards,

Chanel

[marcus_sommer]

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);

next

- Marcus

[#3]

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

[Anonymous]

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?

[marcus_sommer]

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

[pandiarajan]

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.

[marcus_sommer]

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

[#8]

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

[#9]

Hi Chanel,Did you tried my recommended script

Please reply back

[marsman82]

It works like a charm!

[qlikhalmar]

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