Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.
Dear atrapanese,
Would you please give an iteration example to use all rows of "ftpFilesList".
Kind Regards,
Asim Akin
atrapanese, 10X that what I need.
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
Can you share sample QVW file
Can you share sample QVW file@Alan Trapanese