LogParser SQL - basics

[stanl]

LogParser is called the Swiss Army Knife for data processing in the Microsoft arsenal and it is totally free.

http://www.microsoft.com/downloads/d...displaylang=en

By recognizing various file formats for input<>output, LP transforms data from one to the other via standard SQL. I have placed a simple module below which will recuse an entire C: drive for .ppt files and place them in an Access Table with full path, file size and creation date in descending order. You will need [of course] Logparser installed, a blank .mdb file named lp.mdb in the same directory as an Excel file you create then paste in the code. (when you run it it might take some time depending upon the size of your hard drive)

Perhaps it doesn't appear that interesting, but I challenge anyone to post an equal amount of code that performs the same task. With a few tweaks you could parametize the module, allow for creation of mdb, clearing of tables and have yourself a fairly generic routine to gather file information into Access tables. .02 Stan

 
Function FileExists(FullFileName As String) As Boolean 
     ' returns TRUE if the file exists
    FileExists = Len(Dir(FullFileName)) > 0 
End Function 
 
Sub LP_CreateTable() 
    Dim oLog, oInput, oOutput, cMDB As String, cTable As String, cSQL As String 
    cTable = "pptfiles" 'set to hold rows for powerpoint files, you can change this
     'to excelfiles, or txtfiles, just change the extension in the
     'cSQL string [below]
    cMDB = ThisWorkbook.Path & "\lp.mdb" 
    If Not FileExists(cMDB) Then 
        MsgBox cMDB & " doesn't exist!" 
        Exit Sub 
    End If 
    cSQL = "SELECT Path, Size, CreationTime INTO " & cTable & " FROM C:\*.ppt ORDER BY CreationTime DESC" 
    Set oLog = CreateObject("MSUtil.LogQuery") 
    Set oInput = CreateObject("MSUtil.LogQuery.FileSystemInputFormat") 
    oInput.recurse = -1 'total recursion to all subdirectories
    Set oOutput = CreateObject("MSUtil.LogQuery.SQLOutputFormat.1") 
    oOutput.oConnString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & cMDB 
    oOutput.createTable = 1 'this assumes blank MDB exists but table doesn't
     'if you were to write code and knew the table existed
     'but wanted to clear it then oOutput.clearTable = 1
    oLog.ExecuteBatch cSQL, oInput, oOutput 
    Set oInput = Nothing 
    Set oOutput = Nothing 
    Set oLog = Nothing 
End Sub 
 

VBA tags courtesy of www.thecodenet.com

[XL-Dennis]

Stan

Long time no see and I hope it's well with You.

This is really nice stuff! Unfortunately I don't have the time to accept the challenge but in view of what You have done I doubt it will be possible.

When operating on a larger amount of data do You experience any extra ordinary memory consuming (or for that matter any memory leaking)?

All the very best,

[stanl]

Hey, Dennis... thanks for stopping in ....

I ran the code on my HP Laptop (SP3) with 120gb hd and ~2500 sub-dirs. It found 29 .ppt files in various locations and created the Access table in just over 1 minute.

AFAIK: Logparser is one of the best-behaved dll's around, especially with respect to recursion. I have never experienced leakage or memory loss, even though I generally write script code as the LP command line interface can be a bit daunting.

The guy who wrote the Logparser OLEDB input also released a freeware LP input that permits parsing data from within zip files. Where I work I normally zip .xls and .mdb files as daily archives with a parseable naming convention. As they have the same table layouts/sheet structures I can't wait to test an LP archive transformation.

If you do get a chance and have LP installed, it should only take ~3 minutes to test the module I posted.

Hope you are doing well also... I'm just happy to be employed since our 401k dropped to the extent we would have to borrow for a Happy Meal

[XL-Dennis]

Stan,

Thanks for the additional info.

Quote: If you do get a chance and have LP installed, it should only take ~3 minutes to test the module I posted.

OK, I will see if I have some time during the coming week.

As long as I'm around I'm pleased with the life

[xld]

Stan,

I just tried this.

On my desktop 250Gb HDD, it found 202 PPT files in 2.68 seconds.

I tried it for mp3 files on my 1.5TB external HDD, it found 2,114 files in 30.28 seconds.

I think I need to re-evaluate how I grab filenames, this is quite impressive.

[stanl]

Quote:   Originally Posted by: xld I think I need to re-evaluate how I grab filenames, this is quite impressive.

Thanks for trying it out. I have a script I put together in 2006 which uses Logparser to capture event log errors, create a workbook and place trhe data in it which is then formatted as a Pivot Table. If you would care to try it let me know. Stan

[xld]

Yes I would.

Have you tried anything fancy like the reports, graphs yet?

[stanl]

Quote:   Originally Posted by: xld Yes I would. Have you tried anything fancy like the reports, graphs yet?

Certainly. I have attached a partial image file - using even less code than in the example I posted, but just changing the output, I am able to query huge SQL Server tables and and email graphical output [the image is a sample as the data is prioprietary]. You can perform the same task in other ways, but with a lot more complicated code. I'll set up the Event Viewer as a downloadable/installable exe, w/source and post a link.

Stan

[Mavyak]

Is early binding an option with this library? I'd like to see all the available members/methods.

[stanl]

Quote:   Originally Posted by: Mavyak Is early binding an option with this library? I'd like to see all the available members/methods.

If you download it, there is a .chm file where the methods are exposed. As for early binding, I'm not sure it matters all that much since LP does it's own thing and merely sends back results. .02 Stan

[CreganTur]

Stan,

you should really consider writing a couple of LogParser articles- I think the information you provide is invaluable for this tool.

[stanl]

Quote:   Originally Posted by: CreganTur Stan, you should really consider writing a couple of LogParser articles- I think the information you provide is invaluable for this tool.

I wanted to suggest logparser as an alternative to the thread in the excel forum where the OP needs to parse a SAS log, however got some webservice needs... waiting on Aaron to respond to my pseudo-challenge for parsing the ?wsdl for the webservice SOAP query, or actually I was more hoping to trade some approaches to recursion methods to parse the data... that would be the basis for some collaboration Stan

[Oorang]

Wow. I didn't know about this one. Downloaded the tool and gave your code a whirl. Checked you their C# examples etc. I have to say, this is really quality stuff. I agree, you ought to do an article, and maybe a few kb entries on this.

Do you happen to know why the early binding fails without error?

[stanl]

Quote:   Originally Posted by: Oorang Wow. I didn't know about this one. Downloaded the tool and gave your code a whirl. Checked you their C# examples etc. I have to say, this is really quality stuff.

You should try the basics++ code (or just search for Logparser as I posted snippets in other sections). I started a thread on Logparser and SOAP but after several days no one had even read it so I deleted the text. That is cool stuff. Stan

[Oorang]

lol I tried to read it today, and you had removed the text. So I nuked the thread. Should have left it up If you build it they will come... eventually.