Hi all!
Tom -- I knew you'd like it, so I insisted. In my opinion it is the right way that saves you from many further unsafe interventions in the raw data. I'm not very familiar with regular expressions so maybe someone else can suggest more efficient patterns.
The "tempSF" file is only used when importing the data and after that it is completely useless so we could delete it if we want.
The two queries are just a demonstration of how you can display the data of the tables in the desired format. These queries can also be created automatically for each table. I suggest that the values (even the incorrect ones) of the tables remain as they are and their correction is done at the query level. Even the fact that some values are incorrect is information in itself. If you noticed, the dates appear corrected in the queries, and that is the answer to your question for the update the invalid date/time values. But if required, I believe we will easily be able to import them corrected without much cost in efficiency.
The actual number of processed files was indeed something that escaped me. But I didn't notice that the thread had changed pages, and I prepared my own version of the fix:
Code:
Private Sub cmdImportSyslogs_Click()
Dim myDir As String, txt As String
Dim i As Long, r As Long, rr As Long
Dim n As Long, nn As Long
Dim t As Single
Dim myList, temp()
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = CurrentProject.Path & "\*.*"
If .Show Then myDir = .SelectedItems(1)
End With
If myDir = "" Then Exit Sub
myList = SearchFiles(myDir, n, temp())
If IsError(myList) Then MsgBox "No file found": Exit Sub
t = Timer
For i = 1 To n
r = accSysLogs((myList(1, i) & "\") & myList(2, i))
rr = rr + r
If r > 0 Then nn = nn + 1
Next i
If nn > 0 Then
MsgBox IIf(n = nn, n, nn & " out of " & n) & " files were successfully processed. " _
& vbCrLf & "In total " & rr & " sys log records in " & (Timer - t) & " seconds." _
, vbInformation, "Import Sys Log Files"
Else
MsgBox "No sys logs found!", vbExclamation, "Import Sys Log Files"
End If
End Sub
(You don't have to drop the tables at this point. They are dropped by the input function.)
Again: If is still required to import the date/time corrected, let me know to give a try.
Cheers,
John
P.S.: Sorry for the late response but, in Greece, today is the 'Ochi Day', and my village is full of visitors who want to see me.