topic Download file from ftp area in QlikView

Download file from ftp area

Mon, 13 Sep 2010 16:44:25 GMT

Hi all, I have to load from ftp area some files.

This is my script:

let path = 'ftp://user:pwd@ftp_area.dominio.it'

for each File in filelist ('$(path)pattern*.csv')

let CurrentFileName = '$(File)';

QV_Table:

LOAD * FROM [$(CurrentFileName)] (txt, codepage is 1252, embedded labels, delimiter is ';', msq); Next File ;

The script have not errors, but not find any files. (I'm sure that the files are present)

Any suggestion? Thanks

Download file from ftp area

Thu, 30 Sep 2010 14:54:09 GMT

I've a similar problem. I'm trying load several files from an FTP Server. First attempt: to use wildcards in the filename.

LOAD * FROM [ftp://user:pwd@ftp_area.dominio.it/pattern*.csv] (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

But I get "Cannot open file" (everything goes fine without wildcards). Wildcards work fine for local directories.

Using FOR EACH and Filelist I get the same result that dp2000: no error but no file is retrieved.

Seems that Filelist doesn't work with FTP.

Download file from ftp area

Tue, 08 Feb 2011 15:58:19 GMT

This is a possible solution based on VBScript function.

I defined this macro:

Function ftpList(ftpServer, ftpPort, ftpUser, ftpPwd, ftpRemotePath)
Const OpenAsDefault = -2
Const FailIfNotExist = 0
Const ForReading = 1
Const ForWriting = 2

Set oFTPScriptFSO = CreateObject("Scripting.FileSystemObject")
Set oFTPScriptShell = CreateObject("WScript.Shell")

'build input file for ftp command
sFTPScript = sFTPScript & "USER " & ftpUser & vbCRLF
sFTPScript = sFTPScript & ftpPwd & vbCRLF
sFTPScript = sFTPScript & "cd " & ftpRemotePath & vbCRLF
sFTPScript = sFTPScript & "ls" & vbCRLF
sFTPScript = sFTPScript & "quit" & vbCRLF & "quit" & vbCRLF & "quit" & vbCRLF

sFTPTemp = oFTPScriptShell.ExpandEnvironmentStrings("%TEMP%")
sFTPTempFile = sFTPTemp & "\" & oFTPScriptFSO.GetTempName
sFTPResults = sFTPTemp & "\" & oFTPScriptFSO.GetTempName

'Write the input file for the ftp command
'to a temporary file.
Set fFTPScript = oFTPScriptFSO.CreateTextFile(sFTPTempFile, True)
fFTPScript.WriteLine(sFTPScript)
fFTPScript.Close
Set fFTPScript = Nothing

oFTPScriptShell.Run "%comspec% /c FTP -n -v -s:" & sFTPTempFile & " " & ftpServer & " > " & sFTPResults, 0, TRUE

ftpList = sFTPResults
End Function

this macro connects to ftp site using ftp.exe and executes command "ls"; the output is sored in a temporary file. The function returns full path and filename of temporary output file.

You can call this macro in your QlikView script as in this example:

LET tempFile = ftpList('172.23.56.76', '21', 'root', 'root', '/tmp');

ftpFilesList:
LOAD @1 as fileName
FROM
$(tempFile)
(txt, codepage is 1252, no labels, delimiter is ',', msq)
Where Left(@1, 4) <> 'ftp>' and @1 <> 'quit';

let tempFile = null();

This solution stores in a table all files found in FTP server; you can filter results using where conditions; for example, if you need only "*.csv" files, you can add this condition:

Where Left(@1, 4) <> 'ftp>' and @1 <> 'quit' and @1 like '*.csv';

This solution doesn't handle FTP connection errors, but you can chek it in "ftpFilesList" output table.
You can iterate on all rows of "ftpFilesList" table to read al files content usinf "LOAD" syntax

Re: Download file from ftp area

christian77 — Tue, 05 Jun 2012 09:29:03 GMT

Hi atrapanese:

I´ve tried the macro and it works but it only brings the names of the files. Now I need to get the content of the file. I only need a file and I know the name of the file. What should I do?

Thanks a lot.

Re: Download file from ftp area

Mon, 24 Sep 2012 19:38:54 GMT

Dear atrapanese,

Would you please give an iteration example to use all rows of "ftpFilesList".

Kind Regards,

Asim Akin

Re: Download file from ftp area

Tue, 18 Jun 2013 11:42:30 GMT

atrapanese, 10X that what I need.

asim.akin, try this way:

ftpFilesList:

LOAD

@1 as fileName

FROM

$(tempFile)

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

Where

Left(@1, 4) <> 'ftp>'

and @1 <> 'quit'

;

let tempFile = null();

TRACE ==========;

Let vFoldersNo = FieldValueCount('fileName');

TRACE Folders count: $(vFoldersNo);

FOR i=1 to vFoldersNo

Let vFolderName = FieldValue('fileName', i);

TRACE Folder name: $(vFolderName);

Re: Download file from ftp area

pandiarajan — Wed, 02 Apr 2014 06:33:42 GMT

Can you share sample QVW file

Re: Download file from ftp area

pandiarajan — Wed, 02 Apr 2014 06:34:25 GMT

Can you share sample QVW file@Alan Trapanese