Download file from ftp area

[#1]

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

[#2]

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.

[#3]

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

[christian77]

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.

[#5]

Dear atrapanese,

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

Kind Regards,

Asim Akin

[#6]

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

NEXT

[pandiarajan]

Can you share sample QVW file

[pandiarajan]

Can  you share sample QVW file@Alan Trapanese