Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24
  1. #16
    Join Date
    Feb 2019
    Posts
    1,038

    John:

    Good morning... this is a follow-up to my post #15.

    - Again, I really like the improved version of the import routine. See details/comments, again, in post #15.
    - I tweaked the message box to include information about a) files/tables processed and b) total # of files (existing). That'll tell me that n files failed (e.g., do not include actual messages).

    See attached the updated DB to include those changes.

    Finally, I still need to figure out a method as to how to update the invalid hours/min given these are now stored individually in fields [F3] and [F4]. The logic you previously developed still applies but won't work anymore given it was based only on field [F2].

    Thank you,
    Tom
    Attached Files Attached Files

  2. #17
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Wow... this is a very much improved solution to the data import routine. I am extremely impressed w/ this much improved IMPORT routine.
    Much better than band-aids, isn't it.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  3. #18
    Join Date
    Feb 2019
    Posts
    1,038
    @moke123:

    Access Forum = Red Cross

    //

    I will integrate it once I figure out how to transform the invalid dates. See post 15. (The existing version does fix it, but now the table structure is different).

  4. #19
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    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.

  5. #20
    Join Date
    Feb 2019
    Posts
    1,038
    John:

    First of all, happy "Ochi Day". I can image the villages/towns being crowded. Cheers!!

    - Again, I am thrilled about this new import routine.
    - I have summarized "final thoughts" in the attached PDF. I chose that format in hopes of improved readability (not trying to avoid typing my comments out directly in this forum).
    - Upon of the review of the PDF (slide #3 specifically), maybe we can make a more informed decision as to how to fix the invalid date/time issue (either fixing it from the get-go OR fixing it via the query). **

    ** Personally, I don't think I need to track down as to why the data/system owner outputs invalid times. So, while I agree in general that having knowledge of the invalid dates/times could be useful, it may not be necessary for this particular activity. That said, I think I'm leaning towards cleaning it up as part of the input routine. Also, doing so, I still have the ability to chose either method for analysis purposes (query gives me Datetimestamp and DTM while table gives me info across five (5) date/time fields).

    Lastly, I have attached the latest DB (as well as slightly larger sample of syslogs).

    I look forward to you final/additional input. Cheers!

    Tom
    Attached Files Attached Files

  6. #21
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi Tom!

    I think you will also agree that this is my last input.

    Since the goal is to gather records from all files into one table, creating multiple tables, renaming their fields, and finally deleting them, is redundant.

    In the attached "accImport Syslog vZ1.accdb", the T200_Syslog_Merged table is populated with the corrected values ​​in a single step. As you will notice, there are many changes to the code in this version and mostly removals. It's almost a new approach.

    In this version, the contents of the log files are all gathered in the temporary file, inserted with a single command, and from the [temp] table created, inserted into the T200_Syslog_Merged table corrected via the append query.
    As you will see, the now corrected date and time is kept in a field of type date/time and the milliseconds in a separate numeric field. I think now the data is in the ideal format for further analysis and now you know how to present them in the desired format.

    Cheers and good luck with your project,
    John
    Attached Files Attached Files

  7. #22
    Join Date
    Feb 2019
    Posts
    1,038
    Hello John:

    To say the least, I'm super-impressed with this streamlined version. Thank you so very much for having a critical look at the existing process and pointing out some redundancies.

    This version "does it all"... and w/ significantly less code. That's totally awesome. Again, thank you for the continued assistance and offering some many pointers/lessons learned during this thread.

    I very much appreciate your assistance.

    Be blessed,
    Tom

  8. #23
    Join Date
    Feb 2019
    Posts
    1,038
    John:

    First, let me start my apologizing up-front for this follow-up question. In your post #21, you did mention "I think you will also agree that this is my last input."

    With your permission, I have a very quick follow-up... I finally received a new data set. I was wondering if it's possible to modify the import routine so that one of the fields (i.e., [F12]) could import the data as "long text" vs. "short text".

    That is, field [F12] is a "message" field and could include more than 255 characters. That said, if I change the field type in the *temp* table from "short" to "long text", this change is overwritten during each import execution.

    Thus, my question... is there a simple way to change the VBA so that F12 would also be imported as "long text"?

    Thank you,
    Tom

    P.S. If you'd like me to open a new thread, I'll gladly do so.


    Attached Thumbnails Attached Thumbnails TempLog.jpg  

  9. #24
    Join Date
    Feb 2019
    Posts
    1,038
    Post #23 is OBE (overtaken by events). Thank you!

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 04-21-2021, 09:44 AM
  2. Replies: 5
    Last Post: 04-22-2019, 08:55 PM
  3. Replies: 7
    Last Post: 01-25-2015, 02:10 AM
  4. Replies: 1
    Last Post: 10-30-2014, 06:31 AM
  5. Unbound text box data type and "Delete" function
    By gaker10 in forum Programming
    Replies: 16
    Last Post: 06-13-2014, 10:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums