Help with parsing ASP files 2

[WyldeCarde]

Hi all

I have been tasked with writing an Access database that will search a directory and all sub directories for ASP files, find which SQL stored procedures each fo them runs and output this data.

I think in the ASP files the lines I am searching for will be:

cmdAdmin.CommandText = "%stored_procedure_name%"
cmdAdmin.CommandType = adCmdStoredProc

I would need to output the path of the ASP file it was found in, and what the value of %stored_procedure_name% is, each file likely to contain multiple entries.

Help!

 

[Remou]

At one stage I had reason to list ASP files, so here are some notes:

Sub GetASP()
Dim strPath As String
strPath = "C:\Here\"

Debug.Print CountDocs(strPath)
Call ListFiles(strPath)

End Sub

Function CountDocs(strPath) As Long
'References Microsoft Office x.x Object Library

'Counts number of documents to process
'Separated from file process for purposes of messages

Dim lngFileIndex As Long

DoCmd.Hourglass True
With Application.FileSearch
    .NewSearch
    .LookIn = strPath
    .SearchSubFolders = True
    .FileType = 1 'msoFileTypeAllFiles
    .FileName = "*.asp"
    .MatchTextExactly = True
    
    If .Execute() > 0 Then
        CountDocs = .FoundFiles.Count
    Else
        CountDocs = 0
    End If
End With

End Function

Function ListFiles(strPath) As Integer
'References Microsoft Office x.x Object Library

Dim fs As Object

Set fs = CreateObject("Scripting.FileSystemObject")

With Application.FileSearch
    
    If .FoundFiles.Count > 0 Then
        DoCmd.Hourglass True

        For lngFileIndex = 1 To .FoundFiles.Count
            On Error Resume Next
            Set vFile = fs.GetFile(.FoundFiles(lngFileIndex))
            If Err.Number > 0 Then GoTo PrintName
            vDateCreated = vFile.DateCreated
            vDateLastAccessed = vFile.DateLastAccessed
            vDateLastModified = vFile.DateLastModified
            vFileSize = FileLen(.FoundFiles(lngFileIndex))
            
            Set vText = fs.OpenTextFile(vFile)
            vLine = vText.ReadLine
            
            Do While Not vText.AtEndOfStream <> True
                If Left(vLine, 9) = "cmdAdmin." Then
                    Debug.Print vText
                End If
            Loop
            Set vFile = Nothing
PrintName:
            Debug.Print .FoundFiles(lngFileIndex)
            
        Next
    End If
End With

End Function

 

[PHV]

Remou, just a note:
...
If Err.Number > 0 Then GoTo PrintName
...
PrintName:
Debug.Print .FoundFiles(lngFileIndex)
[!]Err.Clear[/!]
Next
...

 

[Remou]

Oops, thanks, PHV

 

[WyldeCarde]

This seems like a good starting point

Probably I am being dim, but if I try and run this from the code window I just get hourglassed at for quite a while and nothing happens.

The only changes I made were the path and PHV's suggestion.

 

[Remou]

What change did you make here:

Sub GetASP()
Dim strPath As String
strPath = "C:\Here\"

Debug.Print CountDocs(strPath)
Call ListFiles(strPath)

End Sub

 

[WyldeCarde]

Sub GetASP()
Dim strPath As String
strPath = "H:\CVS\Electronic_Flight_Bag\"

Debug.Print CountDocs(strPath)
Call ListFiles(strPath)

End Sub

 

[Remou]

Can you see the immediate window? Do you not even get a count printed in the immediate window?

 

[WyldeCarde]

No nothing happens

It eventually seems to finish but the acees window has the hourglass continuously, which I think might mean it's never exiting the with... section

 

[Remou]

If you have a lot of files, CountDocs could take sometime, it is comparable to Windows search for files. You can choose a directory with fewer files to test. If you still do not get any results, step through and tell me where the hold-up occurs.

 

[Remou]

Another though, did you note:

References Microsoft Office 9.0 Object Library

That is, a reference is required?

 

[WyldeCarde]

I did it with a folder with just 11 files in

Hmm seems to be working, all the values are being set correctly as it steps through, its just not outputting anything.

 

[Remou]

What output do you expect? All the code does is debug.print, it is not finished code, just a start for you.

 

[PHV]

Hit Ctrl-G

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WyldeCarde]

OK now I feel stupid, it's working just as advertized, thanks so much for your help.

I've rehashed things so that it outputs the search results to a table, called "file_paths", into a field which is cunningly called "path"

I'm now trying to write another bit of code which will do the second ahlf of the process, scanning each file for the string "cmdAdmin.CommandType = adCmdStoredProc" and then outputting the preceding line.

I've done the majority of the heavy lifting but fallen over at the last hurdle, Ive got it to read the "file_paths" table and output the path and the preceding line mentioned above into the relevant fields in a seperate table (A seperate table as many ASP files will have more than one instance)

However I've got lost in my loops, When I step through it it does the process once as I would want and then exists. It outputs the first line of the destination table.

Option Compare Database

Sub Find_proc()

Dim isFound, TextLine1, TextLine2
Dim rst_read As Recordset
Dim rst_write As Recordset
Dim dbs As Database
Set dbs = CurrentDb
Set rstread = dbs.OpenRecordset("File_Paths")
Set rstwrite = dbs.OpenRecordset("Stored_proc")

'Form_Form1.Text1.SetFocus
'Open Form_Form1.Text1.Text For Input As #1
Open rstread.Path For Input As #1
Line Input #1, TextLine1
rstread.MoveFirst
While Not rstread.EOF

    Do While Not EOF(1)
        Line Input #1, TextLine2
            isFound = InStr(1, TextLine2, "cmdUser.CommandType = adCmdStoredProc", 1)
        If isFound > 0 Then
            'MsgBox (TextLine1)
            rstwrite.AddNew
            rstwrite!Path = rstread!Path
            rstwrite!function_raw = TextLine1
            rstwrite.Update
        Else
            TextLine1 = TextLine2
        End If
    Loop
   rstread.MoveNext
Wend

Close #1

End Sub

I think The inside loop is not resetting, or something?

 

[Remou]

It works for me. Have you set up a test file with a set number of matches?

 

[WyldeCarde]

Yeah It will work for any one file, but only the first file in the tree.

 

[Remou]

Ok.

rstread.MoveFirst
While Not rstread.EOF

[red]Open rstread.Path For Input As #1
Line Input #1, TextLine1
[/red]

    Do While Not EOF(1)
        Line Input #1, TextLine2

 

[WyldeCarde]

I tried something similar already but I gave this a bash, I get a "file already open error" on line

Open rstread.Path For Input As #1

I don't really understand why moving that there would do that :S

 

[WyldeCarde]

Thanks for all your help and patience so far Remou

I'm sure helping along noobs gets old fairly fast.