Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Not applicable

How to fetch files from FTP Location?

Can you help me out what script should be written in Qlikview to fetch files from an FTP location?

Moreover, the file that is required to be fetched has a particular naming format like 'abcd_pqrs_ddmmyyyy' but the problem lies in the fact that the folder location contains many files in this format. The date part in the naming format goes on changing. So whenever in case I will try to generate the report, the latest file should be fetched. Can you please help me achieve this?

Tags (1)
3 Replies
gareth_wilson12
Contributor II

Re: How to fetch files from FTP Location?

Qlik cannot connect to an FTP site out of the box but Qlik do provide a free FTP connector as part of the Qlik Web connectors package;

https://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/2.1/Connectors/Qlik-FT...

This script should then get you started on receiving only the most recent file, this is only for local directories so you will need to adapt for your FTP connection;

////Load File list

GetFiles:

Load

FileName() as Filename

FROM

[..\06_Other\abcd_pqrs_*.txt]

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

NoConcatenate

////Find the most recent file

GetMaxFile:

Load

MaxString(Filename) as Filename

Resident GetFiles;

////Save filename to variable

Let vMaxFile = Peek('Filename',0,'GetMaxfile');

////Pass variable to query to load just one file

LoadOnlyRecentfile:

Load

field

FROM

[..\06_Other\$(vMaxFile)]

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

Drop Table GetFiles;

Drop Table GetMaxFile;

pablo_ortiz
Contributor

Re: How to fetch files from FTP Location?

hello, take a look at this How to fetch Files from an FTP Location?

Not applicable

Re: How to fetch files from FTP Location?

Hi Gareth,

Thanks for the help!!! I am facing a problem. Could you please clarify.

I actually have xml files instead of txt files. Besides I am getting an error "The object was not found".

Could you please rewrite the code if the files were in xml.

I have written something like this:

////Load File list

GetFiles:

Load *,

FilebaseName() as Filename

FROM [ftp://USERNAMESmiley TongueASSWORD@abc.com/u03/home/myname/All_open_2_*.xmlPASSWORD@abc.com/u03/home/myname/All_open_2_*.xml] (XmlSimple);

NoConcatenate

////Find the most recent file

GetMaxFile:

Load

MaxString(Filename) as Filename

Resident GetFiles;

////Save filename to variable

Let vMaxFile = Peek('Filename',0,'GetMaxfile');

 

////Pass variable to query to load just one file

LoadOnlyRecentfile:

Load

doi,

aid,

jid

FROM

[ftp://USERNAMESmiley TongueASSWORD@abc.com/u03/home/myname/PASSWORD@abc.com/u03/home/myname/$(vMaxFile)]

(XmlSimple);

Drop Table GetFiles;

Drop Table GetMaxFile;

Is it ok? Please rectify in case of any errors.

Thanks and Regards,

Arghya

Community Browser