Consulting

Results 1 to 15 of 15

Thread: LogParser SQL - basics

  1. #1
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location

    LogParser SQL - basics

    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

    [vba]
    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]

  2. #2
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    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,
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  3. #3
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    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

  4. #4
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Stan,

    Thanks for the additional info.

    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
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    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

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Yes I would.

    Have you tried anything fancy like the reports, graphs yet?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    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

  9. #9
    VBAX Tutor Mavyak's Avatar
    Joined
    Jul 2008
    Posts
    204
    Location
    Is early binding an option with this library? I'd like to see all the available members/methods.

  10. #10
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    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

  11. #11
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    Stan,

    you should really consider writing a couple of LogParser articles- I think the information you provide is invaluable for this tool.
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  12. #12
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    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

  13. #13
    Knowledge Base Approver VBAX Master Oorang's Avatar
    Joined
    Jan 2007
    Posts
    1,135
    Location
    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?
    Cordially,
    Aaron



    Keep Our Board Clean!
    • Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
    • Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.

  14. #14
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    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

  15. #15
    Knowledge Base Approver VBAX Master Oorang's Avatar
    Joined
    Jan 2007
    Posts
    1,135
    Location
    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.
    Cordially,
    Aaron



    Keep Our Board Clean!
    • Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
    • Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •