Saving ftp directory listing to databse

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Saving ftp directory listing to databse

Logged in as: Guest

arrSession:exec spGetSession 2,2,38463

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Saving ftp directory listing to databse
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1] 2 next > >>

Message

<< Older Topic Newer Topic >>

Saving ftp directory listing to databse - 10/8/2006 11:49:36 PM

caryma77

Posts: 27
Score: 0
Joined: 9/19/2006
Status: offline

I am new to vbs with ftp and I am trying to see if there is a way that I can get a directory listing on a ftp server and save them to a db like Access.

I can connect to the ftp server using the follow

in a text file:

[username]
[password]

then in a bat file:

C:
ftp -i -s:[Drive]:\[Folder]\test.txt [server ip] > [Drive]:\[Folder]\Logfiles.log

I run the batch file and I have connection.

Now I know I can us the ls command to get a list of the directories but how can I use a db connection to write each directory to a table?

I search and found nothing that was close to what I am looking for. Please help

Thanks

Post #: 1

RE: Saving ftp directory listing to databse - 10/9/2006 12:27:31 AM

ehvbs

Posts: 2222
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi caryma77,

what do you mean by "directory listing" resp. "list of the directories" resp. "to write each directory to a table"? Aren't you
interested in files at all? Do you need a recursive listing of all files and directories on the ftp server?

will the command "ls -lR" appended to your text file give you all the information you need?

do you have an existing (Access) database with (a) suitable table(s) or do you want to start from scratch?

(in reply to caryma77)

Post #: 2

RE: Saving ftp directory listing to databse - 10/9/2006 12:40:35 AM

caryma77

Posts: 27
Score: 0
Joined: 9/19/2006
Status: offline

I am not worried about the files yet. I need to get the directories. What I am trying to accomplish is have a table which I can create with two columns {directory, new} and what I want to do is get a list of directories on the ftp server and compare daily to see if one is created. So what I would do is take the current directories as they are now, load them into the table then daily get a list of directories and put them in a temp table and compare the two results.

(in reply to ehvbs)

Post #: 3

RE: Saving ftp directory listing to databse - 10/9/2006 1:01:11 AM

ehvbs

Posts: 2222
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi caryma77,

sorry, but I still have questions:

do you need

- all subdirectories immediately in the root directory

- all subdirectories recursively

could you execute "ls -l" or "ls -lR" against your server and check whether this gives
you the needed information

do you have an existing database or do you want code to create a database with
suitables table(s)?

(in reply to caryma77)

Post #: 4

RE: Saving ftp directory listing to databse - 10/9/2006 1:13:37 AM

caryma77

Posts: 27
Score: 0
Joined: 9/19/2006
Status: offline

ls would give me the information needed. I don't need sub dir. Code to create the database would also be greatly appreciated. ls gives the information need now I just need to know how to write the directories to a db.

(in reply to ehvbs)

Post #: 5

RE: Saving ftp directory listing to databse - 10/9/2006 4:43:53 AM

ehvbs

Posts: 2222
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi carymaa77,

this code

' Consts
   Const adClipString =   2 ' 00000002
   Const SW_SHOWMINNOACTIVE =  7
 
 ' Specs
   Dim sFCFSpec : sFCFSpec = ".\ftpdir.txt"
   Dim sLGFSpec : sLGFSpec = ".\ftpdir.log"
   Dim sDBFSpec : sDBFSpec = ".\ftpdir.mdb"
 
   Dim sServer  : sServer  = "ftp.microsoft.com"
   Dim sUser    : sUser    = "anonymous"
   Dim sPWord   : sPWord   = "ehvbs@vbc.com"
 
   Dim oRE      : Set oRE  = New RegExp
   oRE.Pattern   = "^d.+?(\w+)\s*$"
   oRE.Global    = True
   oRE.MultiLine = True
 
   Dim sWhat    : sWhat    = "DFWCU" ' create DB : FTP get : write table : compare : update
                                     ' KISS way to specify steps during development/testing
                                     ' UCase: do it  | LCase: skip
 
 ' need a FSO for pathes and IO
   Dim oFS : Set oFS = CreateObject( "Scripting.FileSystemObject" )
   sFCFSpec = oFS.GetAbsolutePathName( sFCFSpec )
   sLGFSpec = oFS.GetAbsolutePathName( sLGFSpec )
   sDBFSpec = oFS.GetAbsolutePathName( sDBFSpec )
 
 ' Connection for DB work
   Dim sCS   : sCS       =   "Provider="    + "Microsoft.Jet.OLEDB.4.0" + ";" _
                           + "Data Source=" + sDBFSpec                  + ";"
   Dim oCNCT : Set oCNCT = CreateObject( "ADODB.Connection" )
   Dim oTS
   Dim sSQL
 
   If "D" = Mid( sWhat, 1, 1 ) Then ' create DB and tables
      If oFS.FileExists( sDBFSpec ) Then oFS.DeleteFile sDBFSpec
      CreateObject( "ADOX.Catalog" ).Create sCS
      oCNCT.Open sCS
      oCNCT.Execute "CREATE TABLE FtpDir0 ( sDir VARCHAR( 250 ) CONSTRAINT pmk PRIMARY KEY )"
      oCNCT.Execute "CREATE TABLE FtpDir1 ( sDir VARCHAR( 250 ) CONSTRAINT pmk PRIMARY KEY )"
   Else
      oCNCT.Open sCS
   End If
 
   If "F" = Mid( sWhat, 2, 1 ) Then ' get first level dir listing from ftp server
      Dim sFTPCmd : sFTPCmd =   "open " + sServer + vbCrLf _
                              +           sUser   + vbCrLf _
                              +           sPWord  + vbCrLf _
                              + "ls -l"           + vbCrLf _
                              + "bye"
      Set oTS = oFS.CreateTextFile( sFCFSpec )
      oTS.WriteLine sFTPCmd
      oTS.Close
      sFTPCmd = "%comspec% /c ftp -i -s:" + sFCFSpec + " > """ + sLGFSpec + """"
      WSCript.Echo sFTPCmd
      If oFS.FileExists( sLGFSpec ) Then oFS.DeleteFile sLGFSpec
      CreateObject( "WScript.Shell" ).Run sFTPCmd, SW_SHOWMINNOACTIVE, True
   End If
 
   If "W" = Mid( sWhat, 3, 1 ) Then ' parse log and write to DB
      Dim oMTS : Set oMTS = oRE.Execute( oFS.OpenTextFile( sLGFSpec ).ReadAll )
      Dim oMT
      For Each oMT In oMTS
          WScript.Echo oMT.SubMatches( 0 )
          oCNCT.Execute "INSERT INTO FtpDir1 (sDir ) VALUES ( '" + oMT.SubMatches( 0 ) +  "' )"
      Next
   End If
 
   If "C" = Mid( sWhat, 4, 1 ) Then ' compare FtpDir0 (master) with FtpDir1 (current)
      Dim oRS
 
      WScript.Echo "same directories"
      sSQL =   " Select     C.sDir            " + vbCrLf _
             + " FROM       FtpDir1 AS C      " + vbCrLf _
             + " INNER JOIN FtpDir0 AS M      " + vbCrLf _
             + "         ON C.sDir = M.sDir   "
      WScript.Echo sSQL
      Set oRS = oCNCT.Execute( sSQL )
      If oRS.EOF Then
         WScript.Echo "empty recordset", vbCrLf
      Else
         WScript.Echo oRS.GetString( adClipString, , vbTab, vbCrLf, "NULL" )
      End If
 
      WScript.Echo "new directories"
      sSQL =   " Select    C.sDir            " + vbCrLf _
             + " FROM      FtpDir1 AS C      " + vbCrLf _
             + " LEFT JOIN FtpDir0 AS M      " + vbCrLf _
             + "        ON C.sDir = M.sDir   " + vbCrLf _
             + " WHERE     M.sDir IS NULL    "
      WScript.Echo sSQL
      Set oRS = oCNCT.Execute( sSQL )
      If oRS.EOF Then
         WScript.Echo "empty recordset", vbCrLf
      Else
         WScript.Echo oRS.GetString( adClipString, , vbTab, vbCrLf, "NULL" )
      End If
 
      WScript.Echo "deleted directories"
      sSQL =   " Select    M.sDir            " + vbCrLf _
             + " FROM      FtpDir0 AS M      " + vbCrLf _
             + " LEFT JOIN FtpDir1 AS C      " + vbCrLf _
             + "        ON M.sDir = C.sDir   " + vbCrLf _
             + " WHERE     C.sDir IS NULL    "
      WScript.Echo sSQL
      Set oRS = oCNCT.Execute( sSQL )
      If oRS.EOF Then
         WScript.Echo "empty recordset", vbCrLf
      Else
         WScript.Echo oRS.GetString( adClipString, , vbTab, vbCrLf, "NULL" )
      End If
   End If
 
   If "U" = Mid( sWhat, 5, 1 ) Then ' update FtpDir0 (master) from FtpDir1 (current)
      sSQL = "DELETE FROM FtpDir0"
      oCNCT.Execute sSQL
      sSQL = "INSERT INTO FtpDir0 SELECT * FROM FtpDir1"
      oCNCT.Execute sSQL
   End If
 
   oCNCT.Close

may do what you want. I tried to handle the subparts of your problem as separately as
possible. I used "ftp.microsoft.com" to make it easier for other members interested in
the problem to test the code.

(in reply to caryma77)

Post #: 6

RE: Saving ftp directory listing to databse - 10/9/2006 4:48:04 AM

caryma77

Posts: 27
Score: 0
Joined: 9/19/2006
Status: offline

Thank you. I will try and see if I can get it to work. Once again thanks for your help

(in reply to ehvbs)

Post #: 7

RE: Saving ftp directory listing to databse - 10/10/2006 4:24:30 AM

caryma77

Posts: 27
Score: 0
Joined: 9/19/2006
Status: offline

ehvbs,

I am having a little trouble with the code that you sent. I can pretty much follow the code but when I run it I am having problem with this particular line.

If "W" = Mid( sWhat, 3, 1 ) Then ' parse log and write to DB
     Dim oMTS : Set oMTS = oRE.Execute( oFS.OpenTextFile( sLGFSpec ).ReadAll )
     Dim oMT
     For Each oMT In oMTS
         WScript.Echo oMT.SubMatches( 0 )
         oCNCT.Execute "INSERT INTO FtpDir1 (sDir ) VALUES ( '" + oMT.SubMatches( 0 ) + "' )"
     Next
End If

This line in red is giving me the following error:

"Input past end of file"

Thanks for your assistance in advance.

(in reply to ehvbs)

Post #: 8

RE: Saving ftp directory listing to databse - 10/10/2006 4:55:05 AM

ehvbs

Posts: 2222
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi caryma77,

the error message indicates, that the file sLGFSpec is empty. Can you check?

(in reply to caryma77)

Post #: 9

RE: Saving ftp directory listing to databse - 10/10/2006 5:00:00 AM

caryma77

Posts: 27
Score: 0
Joined: 9/19/2006
Status: offline

I checked and the file is empty.

(in reply to ehvbs)

Post #: 10

RE: Saving ftp directory listing to databse - 10/10/2006 5:04:37 AM

ehvbs

Posts: 2222
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Looks like the execution of the ftp command had problems with the device rerouting. My
command line (first line of output) looks like this:

%comspec% /c ftp -i "-s:C:\wis\_vbs\0506\dev\forum\ftpdir.txt" > "C:\wis\_vbs\0506\dev\forum\ftpdir.log"

How does yours compare?

(in reply to caryma77)

Post #: 11

RE: Saving ftp directory listing to databse - 10/10/2006 5:17:33 AM

caryma77

Posts: 27
Score: 0
Joined: 9/19/2006
Status: offline

In the code I have this:

sFTPCmd = "%comspec% /c ftp -i -s:" + sFCFSpec + " > """ + sLGFSpec + """"

Which the values of the sFCFSpec and sLGFSpec variables

puts it as

"%comspec% /c ftp -i -s:C:\ftpdir.txt > "C:\ftp.log"

(in reply to ehvbs)

Post #: 12

RE: Saving ftp directory listing to databse - 10/10/2006 5:21:58 AM

caryma77

Posts: 27
Score: 0
Joined: 9/19/2006
Status: offline

I forgot to mention that I am also getting this error message before I receive the EOF error message:

Line 1
CHAR 1
ActiveX component can't create object: 'Internet.communication'

(in reply to caryma77)

Post #: 13

RE: Saving ftp directory listing to databse - 10/10/2006 5:26:32 AM

ehvbs

Posts: 2222
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Are you doing a

Set oX = CreateObject( "Internet.communication" )

in your script? I didn't. Do you use the script from a command line (dos box)?

(in reply to caryma77)

Post #: 14

RE: Saving ftp directory listing to databse - 10/10/2006 5:38:51 AM

caryma77

Posts: 27
Score: 0
Joined: 9/19/2006
Status: offline

I am not using the Set oX = CreateObject( "Internet.communication" ) command at all. I saved the code with a vbs extension and run it by just clicking it for now.

The code is below:

' Consts
Const adClipString =   2 ' 00000002
Const SW_SHOWMINNOACTIVE = 7
' Specs
Dim sFCFSpec : sFCFSpec = "C:\ftpdir.txt"
Dim sLGFSpec : sLGFSpec = "C:\ftpdir.log"
Dim sDBFSpec : sDBFSpec = "C:\ftpdir.mdb"
Dim sServer : sServer = "10.85.20.85"
Dim sUser    : sUser    = "username"
Dim sPWord   : sPWord   = "password"
Dim oRE      : Set oRE = New RegExp
oRE.Pattern   = "^d.+?(\w+)\s*$"
oRE.Global    = True
oRE.MultiLine = True
Dim sWhat    : sWhat    = "DFWCU" ' create DB : FTP get : write table : compare : update
                                    ' KISS way to specify steps during development/testing
                                    ' UCase: do it | LCase: skip
' need a FSO for pathes and IO
Dim oFS : Set oFS = CreateObject( "Scripting.FileSystemObject" )
sFCFSpec = oFS.GetAbsolutePathName( sFCFSpec )
sLGFSpec = oFS.GetAbsolutePathName( sLGFSpec )
sDBFSpec = oFS.GetAbsolutePathName( sDBFSpec )
' Connection for DB work
Dim sCS   : sCS       =   "Provider="    + "Microsoft.Jet.OLEDB.4.0" + ";" _
                          + "Data Source=" + sDBFSpec                  + ";"
Dim oCNCT : Set oCNCT = CreateObject( "ADODB.Connection" )
Dim oTS
Dim sSQL
If "D" = Mid( sWhat, 1, 1 ) Then ' create DB and tables
     If oFS.FileExists( sDBFSpec ) Then oFS.DeleteFile sDBFSpec
     CreateObject( "ADOX.Catalog" ).Create sCS
     oCNCT.Open sCS
     oCNCT.Execute "CREATE TABLE FtpDir0 ( sDir VARCHAR( 250 ) CONSTRAINT pmk PRIMARY KEY )"
     oCNCT.Execute "CREATE TABLE FtpDir1 ( sDir VARCHAR( 250 ) CONSTRAINT pmk PRIMARY KEY )"
Else
     oCNCT.Open sCS
End If
If "F" = Mid( sWhat, 2, 1 ) Then ' get first level dir listing from ftp server
     Dim sFTPCmd : sFTPCmd =   "open " + sServer + vbCrLf _
                             +           sUser   + vbCrLf _
                             +           sPWord + vbCrLf _
                             + "ls -l"           + vbCrLf _
                             + "bye"
     Set oTS = oFS.CreateTextFile( sFCFSpec )
     oTS.WriteLine sFTPCmd
     oTS.Close
     sFTPCmd = "%comspec% /c ftp -i -s: " + sFCFSpec + " > """ + sLGFSpec + """"
     WSCript.Echo sFTPCmd
     If oFS.FileExists( sLGFSpec ) Then oFS.DeleteFile sLGFSpec
     CreateObject( "WScript.Shell" ).Run sFTPCmd, SW_SHOWMINNOACTIVE, True
End If
If "W" = Mid( sWhat, 3, 1 ) Then ' parse log and write to DB
     Dim oMTS : Set oMTS = oRE.Execute( oFS.OpenTextFile( sLGFSpec ).ReadAll )
     Dim oMT
     For Each oMT In oMTS
         WScript.Echo oMT.SubMatches( 0 )
         oCNCT.Execute "INSERT INTO FtpDir1 (sDir ) VALUES ( '" + oMT.SubMatches( 0 ) + "' )"
     Next
End If
If "C" = Mid( sWhat, 4, 1 ) Then ' compare FtpDir0 (master) with FtpDir1 (current)
     Dim oRS
     WScript.Echo "same directories"
     sSQL =   " Select     C.sDir            " + vbCrLf _
            + " FROM       FtpDir1 AS C      " + vbCrLf _
            + " INNER JOIN FtpDir0 AS M      " + vbCrLf _
            + "         ON C.sDir = M.sDir   "
     WScript.Echo sSQL
     Set oRS = oCNCT.Execute( sSQL )
     If oRS.EOF Then
        WScript.Echo "empty recordset", vbCrLf
     Else
        WScript.Echo oRS.GetString( adClipString, , vbTab, vbCrLf, "NULL" )
     End If
     WScript.Echo "new directories"
     sSQL =   " Select    C.sDir            " + vbCrLf _
            + " FROM      FtpDir1 AS C      " + vbCrLf _
            + " LEFT JOIN FtpDir0 AS M      " + vbCrLf _
            + "        ON C.sDir = M.sDir   " + vbCrLf _
            + " WHERE     M.sDir IS NULL    "
     WScript.Echo sSQL
     Set oRS = oCNCT.Execute( sSQL )
     If oRS.EOF Then
        WScript.Echo "empty recordset", vbCrLf
     Else
        WScript.Echo oRS.GetString( adClipString, , vbTab, vbCrLf, "NULL" )
     End If
     WScript.Echo "deleted directories"
     sSQL =   " Select    M.sDir            " + vbCrLf _
            + " FROM      FtpDir0 AS M      " + vbCrLf _
            + " LEFT JOIN FtpDir1 AS C      " + vbCrLf _
            + "        ON M.sDir = C.sDir   " + vbCrLf _
            + " WHERE     C.sDir IS NULL    "
     WScript.Echo sSQL
     Set oRS = oCNCT.Execute( sSQL )
     If oRS.EOF Then
        WScript.Echo "empty recordset", vbCrLf
     Else
        WScript.Echo oRS.GetString( adClipString, , vbTab, vbCrLf, "NULL" )
     End If
End If
If "U" = Mid( sWhat, 5, 1 ) Then ' update FtpDir0 (master) from FtpDir1 (current)
     sSQL = "DELETE FROM FtpDir0"
     oCNCT.Execute sSQL
     sSQL = "INSERT INTO FtpDir0 SELECT * FROM FtpDir1"
     oCNCT.Execute sSQL
End If
oCNCT.Close

(in reply to ehvbs)

Post #: 15

RE: Saving ftp directory listing to databse - 10/10/2006 5:47:26 AM

ehvbs

Posts: 2222
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Could you open a dos box, cd to the directory containing the script and call it like

   cscript <yourscript.vbs>

Could you

   (a) execute

           ftp -i -s:C:\ftpdir.txt > "C:\ftp.log"

         in this dos box and check C:\ftp.log

    (b) change

          Dim sWhat    : sWhat    = "DFWCU" ' create DB : FTP get : write table : compare : update

         to

          Dim sWhat    : sWhat    = "DfWCU" ' create DB : FTP get : write table : compare : update

         and run the script - to skip the FTP action and see if given a good ftp.log file
         will make the script happy.

(in reply to caryma77)

Post #: 16

RE: Saving ftp directory listing to databse - 10/10/2006 5:54:21 AM

caryma77

Posts: 27
Score: 0
Joined: 9/19/2006
Status: offline

Okay I have done the following troubleshooting

I ran the script from the command prompt same error.

Then I executed the command from command prompt and nothing was in the log file

and I also changed the F to lower case f and still didn't work. I don't get the internet.communcation error when I do that though.

(in reply to ehvbs)

Post #: 17

RE: Saving ftp directory listing to databse - 10/10/2006 5:55:11 AM

ehvbs

Posts: 2222
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hold your horses! I tested your script and found that

sFTPCmd = "%comspec% /c ftp -i -s: " + sFCFSpec + " > """ + sLGFSpec + """"

has to be changed to

sFTPCmd = "%comspec% /c ftp -i -s:" + sFCFSpec + " > """ + sLGFSpec + """"

(in reply to ehvbs)