How to parse files recursively from subfolders

One of our many customers asked how to write a query against a Microsoft Event Log that is located under a couple of sub-folders. EVT and some of the other input formats don’t have option to recursively search for files in sub folders. But Log Parser Lizard can help by using inline Visual Basic .NET code to write a function that recursively search for files in sub folders and create a string that can be put after the FROM keyword.

Here are two different but similar approaches for this (although there can be others too). These are sample queries and not related to EVT log but you will get the idea. I like more the second one since it creates shorter SQL query.

But you should always check first if the input format has an option to walk trough directories recursively  (most of them do have that functionality).

1. Append each file from sub folders

SELECT TOP 100 * FROM
<%
Dim sb As New System.Text.StringBuilder()
Dim bFirst as Boolean = True

'Append each file from sub folders
Dim DirSearch as Action(Of String, String) = Sub (sDir As String, sPattern as String)
        For Each d As String In System.IO.Directory.GetDirectories(sDir)
            For Each f As String In System.IO.Directory.GetFiles(d, sPattern)
                If Not bFirst Then sb.Append(" ," & vbCRLF) 'Append comma and new line
                bFirst=False
                sb.Append("'")
                sb.Append(System.IO.Path.Combine(d,f))
                sb.Append("'")
            Next
            DirSearch(d, Pattern)
        Next
End Sub

'Call the folder and file pattern for searching recurslively in subfolders
DirSearch("C:\Visual Studio Projects\Coursera", "*.m")

Return sb.ToString()
%>

Sample output:

SELECT TOP 100 * FROM asass asasa
'C:\Visual Studio Projects\Coursera\Machine Learning\ProgrammingEx\mlclass-ex1-007\mlclass-ex1\computeCost.m' ,
'C:\Visual Studio Projects\Coursera\Machine Learning\ProgrammingEx\mlclass-ex1-007\mlclass-ex1\computeCostMulti.m' ,
'C:\Visual Studio Projects\Coursera\Machine Learning\ProgrammingEx\mlclass-ex1-007\mlclass-ex1\ex1.m' ,
'C:\Visual Studio Projects\Coursera\Machine Learning\ProgrammingEx\mlclass-ex1-007\mlclass-ex1\ex1_multi.m' ,
'C:\Visual Studio Projects\Coursera\Machine Learning\ProgrammingEx\mlclass-ex1-007\mlclass-ex1\featureNormalize.m' ,
'C:\Visual Studio Projects\Coursera\Machine Learning\ProgrammingEx\mlclass-ex1-007\mlclass-ex1\gradientDescent.m' ,
'C:\Visual Studio Projects\Coursera\Machine Learning\ProgrammingEx\mlclass-ex1-007\mlclass-ex1\gradientDescentMulti.m' ,
'C:\Visual Studio Projects\Coursera\Machine Learning\ProgrammingEx\mlclass-ex1-007\mlclass-ex1\normalEqn.m' ,
'C:\Visual Studio Projects\Coursera\Machine Learning\ProgrammingEx\mlclass-ex1-007\mlclass-ex1\plotData.m' ,
'C:\Visual Studio Projects\Coursera\Machine Learning\ProgrammingEx\mlclass-ex1-007\mlclass-ex1\submit.m'



2.  Append file pattern to each sub folder

SELECT TOP 100 * FROM
<%
Dim sb As New System.Text.StringBuilder()
Dim bFirst as Boolean = True

'Append file pattern to each sub folder
Dim DirSearch as Action(Of String, String) = Sub (sDir As String, sPattern as String)
        For Each d As String In System.IO.Directory.GetDirectories(sDir)
            If Not bFirst Then sb.Append(" ," & vbCRLF) 'Append comma and new line
            bFirst=False
            
            sb.Append("'")
            sb.Append(System.IO.Path.Combine(d,sPattern))
            sb.Append("'")
            
            DirSearch(d, sPattern)
        Next
        
End Sub

'Call the folder and file pattern for searching recurslively in subfolders
DirSearch("C:\Visual Studio Projects\Coursera", "*.m")

Return sb.ToString()
%>

Sample output:

SELECT TOP 100 * FROM dssdsd
'C:\Visual Studio Projects\Coursera\Machine Learning\*.m' ,
'C:\Visual Studio Projects\Coursera\Machine Learning\ProgrammingEx\*.m' ,
'C:\Visual Studio Projects\Coursera\Machine Learning\ProgrammingEx\mlclass-ex1-007\*.m' ,
'C:\Visual Studio Projects\Coursera\Machine Learning\ProgrammingEx\mlclass-ex1-007\mlclass-ex1\*.m' ,
'C:\Visual Studio Projects\Coursera\Machine Learning\ProgrammingEx\mlclass-ex2-007\*.m' ,
'C:\Visual Studio Projects\Coursera\Machine Learning\ProgrammingEx\mlclass-ex2-007\mlclass-ex2\*.m' ,
'C:\Visual Studio Projects\Coursera\Machine Learning\ProgrammingEx\mlclass-ex3-007\*.m'

Feedback and Knowledge Base