PDA

View Full Version : 2 more for the Road



stanl
09-10-2007, 06:24 AM
Since xlGibbs just posted that this forum was excellent {and I agree} and could be Extended <---- keyword

I have additional LogParser Snippets to (1) create Access Table via ADOX/LogParser to capture directory files (2) Using LogParser to turn CSV into Gif (with optional windows display).

The current Logparser forum is offline {I think they will be coming out with a Vista/64bit version} but it remains a great SQL tool.

One of my first interest in XML was to create structures to contain structures and having examined LogParsers COM extensibility {from the .wsc standpoint, not .NET} it appears you can output .wsc as Ascii files and re-employ as distinct COM Objects to perform a variety of Logpar' sing ["if you catch my meaning... if you get my drift"]

....sorry... couldn't resist the FireSign Theatre reference

Stan:devil2:

stanl
09-10-2007, 10:51 AM
Let's look at csv2gif first... Logparser integrates the chart object (whether Excel is installed or just the free OWC) so you can easily create/display them based on .csv files. The code is more adaptable to an external script, but below are basics in a sub you can place in Excel (csv is in the zip).

Note To self: at first the "*.*" in the sql query string asked if my glass was half empty or half full... these LogParser guys!!!!! :bug:


Sub csv2gif()
cCSV = ActiveWorkbook.Path & "\carinv.csv"
cGIF = ActiveWorkbook.Path & "\carinv.gif"
If Dir(cCSV) = "" Then Exit Sub
If Dir(cGIF) <> "" Then Kill (cGIF)
cSQL = "SELECT Make, count(Make) AS Makes into " & cGIF & " FROM " & cCSV & "*.* group by Make order by Make ASC"
MsgBox cSQL
Set oLog = CreateObject("MSUtil.LogQuery")
Set oInput = CreateObject("MSUtil.LogQuery.CSVInputFormat")
oInput.headerRow = 1
Set oOut = CreateObject("MSUtil.LogQuery.ChartOutputFormat.1")
oOut.ChartTitle = "Break out By Car Make"
oOut.ChartType = "ColumnStacked3D" 'see end of sub for available Chart Types
oOut.View = 1 'this displays chart in window after creation
oLog.ExecuteBatch cSQL, oInput, oOut
Set oOut = Nothing
Set oInput = Nothing
Set oLog = Nothing
End Sub
'chart types
'Line [default]
'LineMarkers
'LineStacked
'LineStackedMarkers
'LineStacked100
'LineStacked100Markers
'Line3D
'LineOverlapped3D
'LineStacked3D
'LineStacked1003D
'SmoothLine
'SmoothLineMarkers
'SmoothLineStacked
'SmoothLineStackedMarkers
'SmoothLineStacked100
'SmoothLineStacked100Markers
'BarClustered
'BarStacked
'BarStacked100
'Bar3D
'BarClustered3D
'BarStacked3D
'BarStacked1003D
'ColumnClustered
'ColumnStacked
'ColumnStacked100
'Column3D
'ColumnClustered3D
'ColumnStacked3D
'ColumnStacked1003D
'Pie
'PieExploded
'PieStacked
'Pie3D
'PieExploded3D
'ScatterMarkers
'ScatterSmoothLine
'ScatterSmoothLineMarkers
'ScatterLine
'ScatterLineMarkers
'ScatterLineFilled
'Bubble
'BubbleLine
'Area
'AreaStacked
'AreaStacked100
'Area3D
'AreaOverlapped3D
'AreaStacked3D
'AreaStacked1003D
'Doughnut
'DoughnutExploded
'RadarLine
'RadarLineMarkers
'RadarLineFilled
'RadarSmoothLine
'RadarSmoothLineMarkers
'StockHLC
'StockOHLC
'PolarMarkers
'PolarLine
'PolarLineMarkers
'PolarSmoothLine
'PolarSmoothLineMarkers

stanl
09-10-2007, 11:48 AM
I know what you must be thinking by now... dumb old stanl... same 10-14 lines, just does something different each time: pray2:

So, here [using about the same 10-14 lines] is an unusual use of Logparser - performing a recursive directory search and placing the data into an Access Table.

For demonstration purposes I also used ADOX to create a new Access DB, as logparser with create the table. The SQL looks for all .xls files on C:, and took a few minutes on my machine; you can change that to *.ppt, *.doc... whatever... Stan


Sub createtable()
'set additional reference to ADOX
cTable = "ExcelFiles"
cSQL = "SELECT Path, Size, CreationTime INTO " & cTable & " FROM C:\*.xls ORDER BY CreationTime DESC"
cMDB = ActiveWorkbook.Path & "\xl.mdb"
If Dir(cMDB) <> "" Then Kill (cMDB)
'quickie to create Access database
cConn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=" & cMDB
Set oCat = CreateObject("ADOX.Catalog")
oCat.Create cConn
Set oCat = Nothing
Set oLog = CreateObject("MSUtil.LogQuery")
Set oInput = CreateObject("MSUtil.LogQuery.FileSystemInputFormat")
oInput.recurse = -1 'total recursion to all subdirectories
Set oOut = CreateObject("MSUtil.LogQuery.SQLOutputFormat.1")
'NOTE: the Jet 4.0 Provider was used to create the MDB, but
'use the ODBC driver to create the table
oOut.oConnString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & cMDB
oOut.createtable = 1 'documentation says values are ON|OFF, but use 1|0
oLog.ExecuteBatch cSQL, oInput, oOut
Set oOut = Nothing
Set oInput = Nothing
Set oLog = Nothing
End Sub