PDA

View Full Version : Loop thru FTP sub-folders, downloading, renaming & uploading their files.



sifar786
04-19-2013, 11:48 AM
Hi Kenneth,

i came across a couple of your posts on FTP using a batch file to download files from an FTP server. I know that you are a celebrated member of this forum and so thought of asking you directly if you know anyway of helping me solve my issue.

Let me summarize my issue:
1] I basically want to logon to an FTP site e.g. ftp: // 123.45.1.21
2] This site has a parent folder say "Job Folder".
3] Under this job folder i have Job number folders say, 2010,2013,2015,2023,2034,2035,2036 etc.
4] Each of these job folders has an excel file with the same name as the job folder e.g. 2010.xls,2013.xls,2015.xls,2023.xls,2034.xls,2035.xls,2036 etc.

What i want to do:
1] Now i want to connect to ftp site.
2] change directory to parent folder.
3] grab sub-folders in an array.
4] loop thru sub-folders - one at a time.
5] download file from sub-folder.
6] open & parse its contents, close it, then upload it back to its sub-folder.
7] end loop.
8] end ftp connection.


Have you worked out anything like this before? I would be most thankful for your assistance.

Kenneth Hobs
04-22-2013, 07:29 AM
Welcome to the forum!

That is alot of questions. It is best to ask one question per thread. That is a good method anyway as it lends itself to solving one problem at a time. Many people will not respond if too many questions are asked at once. When working on a project like that, solving in steps is best. When you post a followup questions, add the link to what might pertain to the new question if needed.

If you have a real ftp site, that helps.

For now, review: http://vbaexpress.com/forum/showthread.php?p=181480

sifar786
04-22-2013, 11:47 AM
Hi Kenneth,

actually my question is how to loop thru sub-folders captured in an array e.g.
If i change to main directory and then grab DIR listing and parse the sub-folders into an array, How do i loop thru these sub-folders one at a time?

/MainDirectory
___ /2012
___ /2015
___ /2021
...
etc

Kenneth Hobs
04-22-2013, 12:17 PM
When getting a DIR list, use Unix methods similar to DOS methods to port the listing to a file. Once you have that file, iterating subfolders is relatively easy.

Obviously, it is easier to help if we had a real ftp site with subfolders and files.

Your syntax command string may be something like this DOS method:

dir *.* /a:d /b > c:\temp\test.txt

sifar786
04-22-2013, 11:57 PM
hi Kenneth,

this doesnt seem to work on FTP.

dir *.* /a:d /b > c:\temp\test.txt

snb
04-23-2013, 01:49 AM
Alternative:


Sub M_snb_ftp_Internet_explorer()
With New InternetExplorer
.Navigate "ftp://www.xxx.eu/domains/XXX.eu/public_html/", "username: password"

Do
DoEvents
Loop Until .ReadyState = 4

For j = 1 To .Document.links.Length - 1
c01 = c01 & vbLf & .Document.links(j)
Next
.Quit
end with

MsgBox Join(Filter(Split(c01, vbLf), ".xls"), vbLf)
End Sub

sifar786
04-23-2013, 03:19 AM
Hi SNB,

Thanks for your reply.
i prefer a DOS method for my work. the above command did not work at the FTP prompt.

so, i used the following:

'Build new FTP .DAT file (over-writes the old one):
Set oTS = oFS.CreateTextFile(strTempDL & "\FFS.dat", True)
With oTS
.writeline "open " & URL
.writeline LoginID
.writeline LoginPW
.writeline "prompt off"
.writeline "mls " & """" & MainFTPFolder & """" & " " & """" & strTempDL & "\out.txt"""
.writeline "quit"
.Close
End With


works well to remove all extraneous characters and text and just give me the sub-folder names which i then put in an array.

the next step is to download the xls files (having same name as sub-folder), one at a time. any help would be most appreciated.

Kenneth Hobs
04-23-2013, 09:41 AM
Please post the text file for help with that part.

Some methods from here might help with some parts.
http://www.vbaexpress.com/forum/showthread.php?t=34206

sifar786
04-23-2013, 10:42 AM
Thanks Kenneth,

I have attached the text file which is an output of the folders using the mls command. each folder contains an xls file which has the same name as the folder. the idea is to loop thru these folders, download each file at a time to open, parse & close it and then rename that file on the folder appending "_processed" to the file name, so that next time it is not processed again.

i split the text file like this:

Application.StatusBar = "Checking the FTP DIR..."
strDIR = FTPDirListing("xxx.xxx.xx.xxx", "MainFolder", "Username", "Password")

If strDIR = Empty Then
MsgBox "Could not get DIR information.", vbCritical, "Error!"
Else
'split string into 1D array containing folder names
ArrSubFolders = Split(Replace(strDIR, vbCr, vbNullString, 1), vbLf)

'redimension array
ReDim Preserve ArrSubFolders(UBound(ArrSubFolders) - 1)

snb
04-23-2013, 11:51 AM
sub M_snb()
sn=filter(split(createobject("scripting.filesystemobject").opentextfile("G:\OF\out.txt").readall,vbcrlf),".",false)

for j=0 to ubound(sn)
workbooks.open "ftp://www.XXX.eu/" & sn(j) & "/" & split(sn(j),"/")(ubound(split(sn(j),"/"))) & ".xls"
next
End Sub

sifar786
04-24-2013, 01:03 AM
Hi SNB,

Wow! this is insanely Awesome! :)

the main problem with opening xls files on ftp server, is the amount of time they take. so to avoid that, i intend downloading a copy of them as i will only be grabbing their data. then i'll kill the downloaded file, and just connect to ftp and rename that same file as e.g. '2010_processed.xls'.

But i am skeptical about the number of times i will be opening and closing an ftp connection for downloading as well as renaming the file.

One question: Can we open a single FTP connection, downloaded unprocessed files one at a time, parse them, then rename them in their ftp folder and finally close ftp connection?

Please suggest any ideas that will optimize code for faster downloading and renaming.

snb
04-24-2013, 03:42 AM
You can use the ftp command mget to download the files you need.
You can use the ftp command rename to rename the downloaded files on the ftp-server.

sifar786
04-24-2013, 03:46 AM
yes, i know. but i want to download only one unprocessed excel file at a time, process it, kill downloaded file and then RENAME that same file on the ftp server appending "filename_Processed.xls" to it.

please note, each folder contains an excel file which has similar name to it. e.g. 2010 folder will contain 2010.xls file.

But my question remains unanswered: will i have to open/close ftp connection for each file to download/rename? is there a better way to do this?

hope this makes sense.

snb
04-24-2013, 05:06 AM
You can change the order of those activities:

In one ftp session:
- rename all the files that have to be processed
- download all these renamed files

After the closing of the ftp-session:
- process all downloaded files
- kill the downloaded files.

The processing order is different, the result isn't.

Kenneth Hobs
04-24-2013, 05:44 AM
Seems like using MGET is the easiest way. In any case, why download one and process it and then delete downloaded file and then rename file on ftp site? Is that order really needed?

Depending on your needs, it is probably easier and more efficient to download all the files at once, and then rename them on the ftp site in one ftp session. After the ftp session is closed, then do your processing on each downloaded file and then Kill/Delete it.

sifar786
04-24-2013, 06:33 AM
Yes, i thought about that approach. but my question is how to download all files lying in different folders viz. 2010,2013,2016 etc in one ftp session, and then renaming such downloaded files in another ftp session?

Kenneth Hobs
04-24-2013, 07:32 AM
You can do that in one of several ways. All caps mean FTP command.

1. MLS (ftp session 1)
2. GET each file from list in MLS (ftp session 2) or use API methods at end.
2. Process local downloaded files.
3. Kill/Delete local downloaded files.
4. REN files on remote server based on MLS names or names in local download folder. (ftp session 3 or API commands)

When using MLS, or especially MGET or GET, put the files all in one local folder for easy handling.

Handy function to just get the local files basename.
Function GetBaseName(filespec As String)
Dim fso As Object, s As String
Set fso = CreateObject("Scripting.FileSystemObject")
s = fso.GetBaseName(filespec)
Set fso = Nothing
GetBaseName = s
End Function

These API methods are from the API guide. You need to modify some parts.
Const FTP_TRANSFER_TYPE_UNKNOWN = &H0
Const FTP_TRANSFER_TYPE_ASCII = &H1
Const FTP_TRANSFER_TYPE_BINARY = &H2
Const INTERNET_DEFAULT_FTP_PORT = 21 ' default for FTP servers
Const INTERNET_SERVICE_FTP = 1
Const INTERNET_FLAG_PASSIVE = &H8000000 ' used for FTP connections
Const INTERNET_OPEN_TYPE_PRECONFIG = 0 ' use registry configuration
Const INTERNET_OPEN_TYPE_DIRECT = 1 ' direct to net
Const INTERNET_OPEN_TYPE_PROXY = 3 ' via named proxy
Const INTERNET_OPEN_TYPE_PRECONFIG_WITH_NO_AUTOPROXY = 4 ' prevent using java/script/INS
Const MAX_PATH = 260
Private Type FILETIME
dwLowDateTime As Long
dwHighDateTime As Long
End Type
Private Type WIN32_FIND_DATA
dwFileAttributes As Long
ftCreationTime As FILETIME
ftLastAccessTime As FILETIME
ftLastWriteTime As FILETIME
nFileSizeHigh As Long
nFileSizeLow As Long
dwReserved0 As Long
dwReserved1 As Long
cFileName As String * MAX_PATH
cAlternate As String * 14
End Type
Private Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As Long) As Integer
Private Declare Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" (ByVal hInternetSession As Long, ByVal sServerName As String, ByVal nServerPort As Integer, ByVal sUserName As String, ByVal sPassword As String, ByVal lService As Long, ByVal lFlags As Long, ByVal lContext As Long) As Long
Private Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" (ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, ByVal sProxyBypass As String, ByVal lFlags As Long) As Long
Private Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias "FtpSetCurrentDirectoryA" (ByVal hFtpSession As Long, ByVal lpszDirectory As String) As Boolean
Private Declare Function FtpGetCurrentDirectory Lib "wininet.dll" Alias "FtpGetCurrentDirectoryA" (ByVal hFtpSession As Long, ByVal lpszCurrentDirectory As String, lpdwCurrentDirectory As Long) As Long
Private Declare Function FtpCreateDirectory Lib "wininet.dll" Alias "FtpCreateDirectoryA" (ByVal hFtpSession As Long, ByVal lpszDirectory As String) As Boolean
Private Declare Function FtpRemoveDirectory Lib "wininet.dll" Alias "FtpRemoveDirectoryA" (ByVal hFtpSession As Long, ByVal lpszDirectory As String) As Boolean
Private Declare Function FtpDeleteFile Lib "wininet.dll" Alias "FtpDeleteFileA" (ByVal hFtpSession As Long, ByVal lpszFileName As String) As Boolean
Private Declare Function FtpRenameFile Lib "wininet.dll" Alias "FtpRenameFileA" (ByVal hFtpSession As Long, ByVal lpszExisting As String, ByVal lpszNew As String) As Boolean
Private Declare Function FtpGetFile Lib "wininet.dll" Alias "FtpGetFileA" (ByVal hConnect As Long, ByVal lpszRemoteFile As String, ByVal lpszNewFile As String, ByVal fFailIfExists As Long, ByVal dwFlagsAndAttributes As Long, ByVal dwFlags As Long, ByRef dwContext As Long) As Boolean
Private Declare Function FtpPutFile Lib "wininet.dll" Alias "FtpPutFileA" (ByVal hConnect As Long, ByVal lpszLocalFile As String, ByVal lpszNewRemoteFile As String, ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean
Private Declare Function InternetGetLastResponseInfo Lib "wininet.dll" Alias "InternetGetLastResponseInfoA" (lpdwError As Long, ByVal lpszBuffer As String, lpdwBufferLength As Long) As Boolean
Private Declare Function FtpFindFirstFile Lib "wininet.dll" Alias "FtpFindFirstFileA" (ByVal hFtpSession As Long, ByVal lpszSearchFile As String, lpFindFileData As WIN32_FIND_DATA, ByVal dwFlags As Long, ByVal dwContent As Long) As Long
Private Declare Function InternetFindNextFile Lib "wininet.dll" Alias "InternetFindNextFileA" (ByVal hFind As Long, lpvFindData As WIN32_FIND_DATA) As Long
Const PassiveConnection As Boolean = True
Private Sub Form_Load()
'KPD-Team 2000
'URL: http://www.allapi.net
'E-Mail: KPDTeam@allapi.net
Dim hConnection As Long, hOpen As Long, sOrgPath As String
'open an internet connection
hOpen = InternetOpen("API-Guide sample program", INTERNET_OPEN_TYPE_PRECONFIG, vbNullString, vbNullString, 0)
'connect to the FTP server
hConnection = InternetConnect(hOpen, "your ftp server", INTERNET_DEFAULT_FTP_PORT, "your login", "your password", INTERNET_SERVICE_FTP, IIf(PassiveConnection, INTERNET_FLAG_PASSIVE, 0), 0)
'create a buffer to store the original directory
sOrgPath = String(MAX_PATH, 0)
'get the directory
FtpGetCurrentDirectory hConnection, sOrgPath, Len(sOrgPath)
'create a new directory 'testing'
FtpCreateDirectory hConnection, "testing"
'set the current directory to 'root/testing'
FtpSetCurrentDirectory hConnection, "testing"
'upload the file 'test.htm'
FtpPutFile hConnection, "C:\test.htm", "test.htm", FTP_TRANSFER_TYPE_UNKNOWN, 0
'rename 'test.htm' to 'apiguide.htm'
FtpRenameFile hConnection, "test.htm", "apiguide.htm"
'enumerate the file list from the current directory ('root/testing')
EnumFiles hConnection
'retrieve the file from the FTP server
FtpGetFile hConnection, "apiguide.htm", "c:\apiguide.htm", False, 0, FTP_TRANSFER_TYPE_UNKNOWN, 0
'delete the file from the FTP server
FtpDeleteFile hConnection, "apiguide.htm"
'set the current directory back to the root
FtpSetCurrentDirectory hConnection, sOrgPath
'remove the direcrtory 'testing'
FtpRemoveDirectory hConnection, "testing"
'close the FTP connection
InternetCloseHandle hConnection
'close the internet connection
InternetCloseHandle hOpen
End Sub
Public Sub EnumFiles(hConnection As Long)
Dim pData As WIN32_FIND_DATA, hFind As Long, lRet As Long
'set the graphics mode to persistent
Me.AutoRedraw = True
'create a buffer
pData.cFileName = String(MAX_PATH, 0)
'find the first file
hFind = FtpFindFirstFile(hConnection, "*.*", pData, 0, 0)
'if there's no file, then exit sub
If hFind = 0 Then Exit Sub
'show the filename
Me.Print Left(pData.cFileName, InStr(1, pData.cFileName, String(1, 0), vbBinaryCompare) - 1)
Do
'create a buffer
pData.cFileName = String(MAX_PATH, 0)
'find the next file
lRet = InternetFindNextFile(hFind, pData)
'if there's no next file, exit do
If lRet = 0 Then Exit Do
'show the filename
Me.Print Left(pData.cFileName, InStr(1, pData.cFileName, String(1, 0), vbBinaryCompare) - 1)
Loop
'close the search handle
InternetCloseHandle hFind
End Sub
Sub ShowError()
Dim lErr As Long, sErr As String, lenBuf As Long
'get the required buffer size
InternetGetLastResponseInfo lErr, sErr, lenBuf
'create a buffer
sErr = String(lenBuf, 0)
'retrieve the last respons info
InternetGetLastResponseInfo lErr, sErr, lenBuf
'show the last response info
MsgBox "Error " + CStr(lErr) + ": " + sErr, vbOKOnly + vbCritical
End Sub

snb
04-24-2013, 07:40 AM
this way you can create a string for the ftp session:

Sub M_snb()
sn=filter(split(createobject("scripting.filesystemobject").opentextfile("G:\OF\out.txt").readall,vbcrlf),".",False)

For j=0 To UBound(sn)
c00=c00 & replace("|cd " & sn(j) & "|get " & sn(j) & ".xls|rename " & sn(j) & ".xls " & sn(j) & "_processed.xls","|",vbcrlf)
Next

msgbox mid(c00,2)
End Sub

sifar786
04-25-2013, 01:45 AM
There are altogether 300 folders so 300 files to,
download (ftp),
open (local),
parse (local),
close (local),
rename (ftp).

Kenneth,

I am confused now on how to use the code for my program which you submitted here.


Snb,

I understand that you are transferring output of one command as input to another, just like in DOS. But what does msgbox mid(c00,2) return? and where do i plugin this in my code?

sorry, i am confused as i need a working example which i can then modify to suit my needs.

Thanking you for your patience.

snb
04-25-2013, 02:46 AM
The msgbox is only meant to show you the result of the code.

Why are you asking for advice if you neglect it ? (see KH's and my 'working order' suggestions)

MikeBlane
04-20-2014, 01:29 PM
I like the idea of the "FTPDirListing" function, but where is the "FTPDirListing" defined?