Downloading from Secure FTP site

[Rich1968]

I have data coming from a secure ftp site. I have been doing this manually but it's getting old real quick. Im looking for ways to automate this. I want to be able to download multiple files into a single table in my database. Im looking for suggestions. I have been on the forum sites all day long and to be frank im going cross eyed

Any suggestions and examples would be apprciated

Thanks
Rich De Gray

 

[AccessMSSQL]

You can create a batch file that runs and connects to ftp automatically to download the files. I'll post an example shortly.

 

[AccessMSSQL]

Have a look at these sites:

http://www.dostips.com/DtTipsFtpBatchScript.php

Here's a thread that might help also:
http://www.pcreview.co.uk/forums/set-up-simple-ftp-download-batch-job-t2345081.html

 

[Rich1968]

Thanks for the reply, Never thought of using a batch file, However it solve a bunch of code issues. Thanks.

I can't get the download batch to work correctly. Is ther any way I can run this and have it step thru the process? Like in VB?

Thanks
Rich

Here's what I have so far

@Echo Off

REM -- Define File Filter, i.e. files with extension .txt
Set FindStrArgs=/E /C:".txt"

REM -- Extract Ftp Script to create List of Files
Set "FtpCommand=ls"
Call:extractFileSection "[Ftp Script 1]" "-">"%temp%\%~n0.ftp"
Rem Notepad "%temp%\%~n0.ftp"

REM -- Execute Ftp Script, collect File Names
Set "FileList="
For /F "Delims=" %%A In ('"Ftp -v -i -s:"%temp%\%~n0.ftp"|Findstr %FindStrArgs%"') Do (
Call Set "FileList=%%FileList%% "%%A""
)

REM -- Extract Ftp Script to download files that don't exist in local folder
Set "FtpCommand=mget"
For %%A In (%FileList%) Do If Not Exist "%%~A" Call Set "FtpCommand=%%FtpCommand%% "%%~A""
Call:extractFileSection "[Ftp Script 1]" "-">"%temp%\%~n0.ftp"
Rem Notepad "%temp%\%~n0.ftp"

For %%A In (%FtpCommand%) Do Echo.%%A

REM -- Execute Ftp Script, download files
ftp -i -s:"%temp%\%~n0.ftp"
Del "%temp%\%~n0.ftp"
GOTO:EOF


:extractFileSection StartMark EndMark FileName -- extract a section of file that is defined by a start and end mark
:: -- [IN] StartMark - start mark, use '...:S' mark to allow variable substitution
:: -- [IN,OPT] EndMark - optional end mark, default is first empty line
:: -- [IN,OPT] FileName - optional source file, default is THIS file
:$created 20080219 :$changed 20100205 :$categories ReadFile
:$source ftp.residentscreening.net/TOCMS
SETLOCAL Disabledelayedexpansion
set "bmk=%~1"
set "emk=%~2"
set "src=%~3"
set "bExtr="
set "bSubs="
if "%src%"=="" set src=%~f0& rem if no source file then assume THIS file
for /f "tokens=1,* delims=]" %%A in ('find /n /v "" "%src%"') do (
if /i "%%B"=="%emk%" set "bExtr="&set "bSubs="
if defined bExtr if defined bSubs (call echo.%%B) ELSE (echo.%%B)
if /i "%%B"=="%bmk%" set "bExtr=Y"
if /i "%%B"=="%bmk%:S" set "bExtr=Y"&set "bSubs=Y"
)
EXIT /b


[Ftp Script 1]:S
!Title Connecting...
open FTP.residentscreening.net
Username CMS
password Sunshine ( not the real password)

!Title Preparing...
cd TOCMS
lcd c:/Core/*.*
binary
hash

!Title Processing... %FtpCommand%
%FtpCommand%

!Title Disconnecting...
disconnect
bye

 

[AccessMSSQL]

You can open a command prompt and try running the script manually to see what errors it is producing. Are you able to download the files successfully?

 

[Rich1968]

No, I have not been able to download any of the files. I'll try the cmd prompt.

Thanks

 

[AccessMSSQL]

are you using mget in your script?

 

[Rich1968]

yes I'm using mget in the script. but I am having a problem logging on to the ftp site. It's a secure site. The password is masked so I think theres an issue there also.

I'm determined to keep working the problem. At least i'll learn something in the process.


Thanks
Rich1968