Parse FileSystem Information into Access Table

 Keywords: LogParser Parse FileSystem Information MDB Access Table CreationTime MSUtil.LogQuery FileSystemInputFormat SQLOutputFormat

;Microsoft LogParser 2.2 - SQL Output
;
;Parses FileSystem Information into Access Table
;adjusts datatypes according to
;Log Parser Data Type   New Table     Existing Table
;INTEGER                int           int, bigint, smallint, tinyint, bit1
;REAL                   real          real, decimal, float
;STRING                 varchar(n2)   varchar(n), nvarchar(n), char
;TIMESTAMP              datetime      datetime, smalldatetime, date, time
;NULL                   varchar       any type
;
;Stan Littlefield, December 30, 2006
;//////////////////////////////////////////////////////////////////////////////////////

GoSub udfs
;powerpoint files
cTable = "pptfiles"
cSQL = "SELECT Path, Size, CreationTime INTO %cTable% FROM C:\*.ppt ORDER BY CreationTime DESC"
cMDB = StrCat(DirScript(),"LogParser.mdb")
cConn="Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%cMDB%"
tblexist=0 ;Log Parser can create table on its own
           ;we start out assuming it doesn't exist, but crMDB() UDF handles resetting if it does

updatetable = 1 ;set this next constant to 1 to clear table if it exists, before adding rows


BoxOpen("Please Wait...",StrCat("Processing LogParser Query Into Access Table %cTable%",@CRLF,cMDB) )

If ! CrMDB() Then Exit
oLog = CreateObject("MSUtil.LogQuery")
oInput = CreateObject("MSUtil.LogQuery.FileSystemInputFormat")
oInput.recurse = -1 ;total recursion to all subdirectories


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%"
If tblexist
   If updatetable Then oOut.clearTable = 1
Else
   oOut.createTable = 1   ;documentation says values are ON|OFF, but use 1|0
EndIf
oLog.ExecuteBatch(cSQL, oInput, oOut)
oLog=0

BoxShut()
Exit
;//////////////////////////////////////////////////////////////////////////////////////

:udfs

;Either creates a new MDB or checks for
;table if mdb already exists, and clears contents
;if requested
#DefineSubRoutine CrMDB()
retval=0
oCAT = CreateObject("ADOX.Catalog")
If ! FileExist(cMDB)
   BoxTitle("MDB File Does Not Exist...Creating")
   oCAT.Create( cConn )
   updatetable=0
   If FileExist(cMDB) Then retval=1
Else
   oCAT.ActiveConnection=cConn
   Tbls = oCAT.Tables
   For i=0 To (Tbls.count-1)
      If oCAT.Tables(i).Name == cTable Then tblexist=1
   Next
   If tblexist==0
      updatetable=0
   Else
      If updatetable
         BoxTitle("Table Exists...Clearing and Overwriting")
      Else
         BoxTitle("Table Exists...Adding parsed entries")
      EndIf
   EndIf
   Tbls=0
   retval=1
EndIf
oCAT=0
Return( retval )
#EndSubRoutine

Return
;//////////////////////////////////////////////////////////////////////////////////////

Article ID:   W18086

Filename:   Parse FileSystem Information into Access Table.txt

File Created: 2009:03:26:12:49:26

Last Updated: 2009:03:26:12:49:26

Database Search

TechHome

Parse FileSystem Information into Access Table