Tom,
Before you come back with a new TRIM thread, and before you create your own "TRIM TEAM", please give a try to the function below with an original log file:
Code:
Function accSysLogs(ByVal strLogFile As String) As Long
'Prepares the text from strLogFile to import in CurrentDB as CSV file.
Dim strSQL As String, strTable As String, strRet As String
Dim db As DAO.Database
Dim RegX As Object
Dim ff As Long
If Len(Dir(strLogFile)) > 0 Then
On Error Resume Next
ff = FreeFile
Open strLogFile For Input As #ff
strRet = Input(LOF(ff), ff)
Close #ff
If Len(strRet) Then
Set RegX = CreateObject("VBScript.RegExp")
With RegX
.Global = True
'Remove useless parts.
.Pattern = "(<.*\n*)|\[ |((\n.*)$)"
strRet = .Replace(strRet, "")
'Separate any first log line in "fields" with commas
'(also explode time in its parts)
.Pattern = " \$ *| *\]\n|((?!\d):(?=\d))| +(?=.*\$.*\:)"
strRet = .Replace(strRet, ",")
'Remove last "new line(s)" if exists
.Pattern = "\n*$"
strRet = .Replace(strRet, "")
'Add lines
.Pattern = "\n"
strRet = .Replace(strRet, vbCrLf)
.Pattern = "\w," 'Check pattern for comma(s)
End With
If RegX.test(strRet) Then 'There is a comma.
'Create temp file to import.
ff = FreeFile
Open CurrentProject.Path & "\tempSF.txt" For Output As #ff
Print #ff, strRet
Close #ff
If Err = 0 Then
strLogFile = Mid$(strLogFile, InStrRev(strLogFile, "\") + 1)
strTable = "tblSysLog_" & Left$(strLogFile, Len(strLogFile) - 4)
'Import sys log records.
strSQL = "SELECT * INTO " & strTable _
& " FROM [Text;HDR=NO;FMT=Delimited;Database=" _
& CurrentProject.Path & "\].tempSF.txt"
Set db = CurrentDb
db.Execute "DROP TABLE " & strTable
db.Execute strSQL
db.TableDefs.Refresh
accSysLogs = db.RecordsAffected
Set db = Nothing
End If
End If
End If
Set RegX = Nothing
If (Err <> 0) And (Err <> 3376) Then Debug.Print "Import " _
& strLogFile & " ERROR #" & Err & " " & Err.Description
On Error GoTo 0
End If
End Function
For example, in immediate window, type:
Code:
?accSysLogs("C:\???\ac.log")
(replace the ??? with the actual path of sysLogs folder)
and hit Enter.
After that, open the query below:
Code:
SELECT [F1],
Format([F2]+TimeSerial([f3],[f4],[f5])+([f6]/84600000),"yyyy/mm/dd hh:nn:ss:") & Format([f6],"000") AS DATETIMESTAMP,
([F2]+TimeSerial([f3],[f4],[f5])+([f6]/84600000)) AS DTM, F7, F8, F9, F10, F11
FROM tblSysLog_ac;
and let us know if the results are the desired.
Cheers,
John