Microsoft Excel Webinar

Results 1 to 15 of 15

Thread: LogParser SQL - basics

  1. #1

    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

    VB:
     
    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 
    
    
    Formatting tags added by mark007

  2. #2
    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
    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
    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 xld's Avatar
    Joined
    Apr 2005
    Posts
    23,572
    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
    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 xld's Avatar
    Joined
    Apr 2005
    Posts
    23,572
    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
    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
    Is early binding an option with this library? I'd like to see all the available members/methods.

  10. #10
    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
    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
    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
    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
    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
    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
  •