topic Re: Test if file exists for files on FTP in QlikView

Test if file exists for files on FTP

Tue, 16 Jun 2015 13:51:22 GMT

Hi,

The files I want are on FTP, so I can't use asterisk to load them. The file name rule is Logs_YYYY_MM_DD. I used date as variable and loop through to get files in the past 7 days, the problem is I am not able to check if a file exists before load it. I tried filesize but that wouldn't work for ftp files, any suggestions please? Thanks!

Re: Test if file exists for files on FTP

marcus_sommer — Tue, 16 Jun 2015 13:58:13 GMT

Maybe it's easier to copy all files into a local folder and load from them. Have also a look here: Re: For Each File in Filelist - FTP load is not working?

- Marcus

Re: Test if file exists for files on FTP

avinashelite — Tue, 16 Jun 2015 13:58:24 GMT

can you share the script how you reading the files , that will be helpful to answer your question

Re: Test if file exists for files on FTP

Tue, 16 Jun 2015 14:05:10 GMT

Sorry that's not an option

Re: Test if file exists for files on FTP

petter — Tue, 16 Jun 2015 14:07:33 GMT

I would recommend to use something else than the load scripts own FTP transfer. You can use built in Windows features like ftp.exe - the command line ftp client that is part of Windows. You can use Powershell which is part of Windows too and via the .NET Framework you can do FTP easily. There are very good open source solutions too like WinScp, Wget and CURL which are free and well documented. With all these you can script your FTP reliably and get remote directory listings too.

But if you are in a hurry and needs only a one-off solution you could stick to QlikView Load Script FTP - but I abandoned it myself for lack of flexibility, reliability and more.

Re: Test if file exists for files on FTP

Tue, 16 Jun 2015 14:12:09 GMT

Let vNumberOfDays = 7;

For vI = 1 to $(vNumberOfDays)

LET vFileDate=date(today()-vI, 'MMDDYYYY');

LET vFileName='[$(vPath)/logs_$(vFileDate).csv]';

==> Want to check existence here

Log_$(vFileDate):

LOAD *

FROM $(vFileName)

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

;

STORE Log_$(vFileDate) INTO $(vQVD)/Log_$(vFileDate).qvd;

DROP TABLE Log_$(vFileDate);

Next vI

Re: Test if file exists for files on FTP

nicolett_yuri — Tue, 16 Jun 2015 14:13:56 GMT

Use a script to download all files to local directory then create a structure to find all files in this directory

Re: Test if file exists for files on FTP

jonas_rezende — Tue, 16 Jun 2015 14:50:18 GMT

Hi, Jessica.

If your wish is check the existence of files, will could make so before any action:

SWITCH IsNull(filetime('$(Path)\$(v_file).txt'))

CASE -1 //Not Exists

[Insert code here!]

CASE 0 // Exists

[Insert code here!]

DEFAULT //Define messenge or variable.

END Switch;

Hope this help!

Re: Test if file exists for files on FTP

Tue, 16 Jun 2015 15:18:11 GMT

Thanks, but FileTime doesn't work for FTP files when I tried...any ideas?

Re: Test if file exists for files on FTP

jonas_rezende — Tue, 16 Jun 2015 18:33:52 GMT

Hi.

I understand that must make how Yuri Nicolett said. After, develop check file.