I am working on a new project that requires importing SYSLOG files into matrix format for analysis purposes. Initial review of SYSLOG files (https://www.paessler.com/it-explained/syslog) provided some helpful information about the components.
Now, attached is very fundamental SYSLOG sample file. This sample file does NOT include any components (e.g., facility code, severity code, etc.). For now, that's ok though. Presently -- and this is where I need some help -- the utilization of "text delimitation" does NOT import the sample data into the desired format.
Here's what I've done thus far:
- Use text delimitation using "space".
- Altough some information (e.g., rows staring with the "[" bracket) appears to be aligned properly.
- Alternatively, rows immediately underneath the "[" are spread out across multiple columns (obviously, this is due to the space delimiter).
Additional Info:
- I am very vaguely familiar with REGEXP and I've never put it to use.
- It is my understanding that REGEXP may be able to facilite/solve the correct importing into the desired matrix format based on various patterns/rules.
My initial goal:
- All lines starting with "<---- Start" AND "<---- END" should be EXCLUDED from the import routine. These are nothing else but open/close markers and should NOT be needed for the analysis.
- Allow lines marked in yellow (again, underneath the "[" line) should be in a single cell.
- Next, all yellow lines are -- I believe -- really part of the individual message. So, these should be part of the same line via moving them one (1) row up. For example, content (single cell) of row #2 should be moved into next available cell in row #1 (e.g. cell L1).
- Finally, the blue marked data (dates and time) should NOT be separated. Instead, they should a single date-time element.
Does anyone know how to import the example data -- via RegExp -- into a suitable matrix format where alike data elements are stored in the same columns?
Thank you.
EEH
Last edited by skydivetom; 10-18-2022 at 08:10 AM.
jindo -- I appreciate the response. Yes, providing an XLSX makes sense... please see attached XLSX incl. the "Before" and "After" tabs.
Please note the following:
- The data is based on only a few examples. Thus, on the "Before" tab, there could be a scenario where values (currently marked in blue rows) extend beyond column K.
- If so, the concatenation must extend to the last populated cell for, e.g., rows 3, 6, 9, 12, 15, 18, 21, 24, etc.
Also, assuming that such cannot be achieved w/ text delimitation, I only could imagine that VBA may come to mind. If so, that actually would be perfect as I could use a form to execute the procedure.
WOW!!!! Your VBA code is absolutely impressive. Allow me to share what I have tested thus far. Also, I do have a few follow-up questions.
Superb Processing:
- The VBA code is 100% flexible.
- That is, I tried other log files with fewer/more space-separated columns (in the 1st actual line). It worked perfectly for each log file.
To help me understand the VBA code, would you be willing to add some comments to critical lines?":
- For the 1st message line, which code separates them into columns? For the 2nd line, which code concatenates n column into single field?
- Which code/line is the one that deletes, e.g., the open/close brackets and $ sign?
- Which code/line moves the 2nd line into the last populated cell of the previous line?
Other Question #1:
a. When going back to the originally sent SYSLOGs, I realized their file extension is "LOG" vs. "TXT". For testing purposes, I converted all LOG files into TXT files. However, this could be a time-consuming effort.
b. Based on a), is there a way the routine would process the files as LOG files?
c. I tried changing ("TextFiles,*.txt") to ("TextFiles,*.LOG") but that resulted in an error.
Other Question #2:
a. Also, some original files only contain the open/close lines (w/o any actual message). These result in "Run-time error '1004' - Application-defined or objects-defined error."
b. Thus, is there a way to throw a message box (vs. run-time error) such as, e.g., "Log file does not include any message content."?
Thousand thanks in advance!!
EEH
Attached are 2 files with the following:
1. "ac.LOG" -- this is an original file before I changed the file extension from ".log" to ".txt".
2. "ac_packet.txt" -- this file is empty. This is the one that should throw the message box "Log file does not include any message content."
Last edited by skydivetom; 10-18-2022 at 01:13 PM.
Do a step debug,
while you are in vbe;
GoTo [View] - [LocalWindow]
click somewhere on the code and hit F8,
As you hit F8, it executes one line and you see all the variables in LocalWindow, so that you can see what it is doing.
First of all, thank you sooo very much for assisting me w/ this import routine. IT REALLY, REALLY WORKS GREAT!!!
As I have a somewhat of a testing background, I tried various scenarios to ensure all works well. I'll summarize below:
1. All existing LOG formats (AC, AC AUDIT, PS, PS AUDIT) are loaded successfully. All LOG file types populate columns A:H. Great!
2. When attempting to import *empty* files, the message box is thrown. Great!
Follow-Up -- please see tab "Info" for additional details:
- As indicated, all message file types populate columns A:H.
- Now, **for testing only**, I copied file type "ac.log" and named it "testing_additional_columns.log".
- Next, I **arbitrarily** inserted values (xyz_1 abc_1) in between, e.g., "LoginAction" and "$Revision:"
- Now, when importing file "testing_additional_columns", it does recognize those 2 additional columns, and again, content is displayed between columns A:H.
- However, as part of inserting theses two (2) arbitrary columns, two (2) other columns (e.g., "Revision" and "execute()" are dropped.
My question:
- Although the insertion of these 2 arbitary columns is NOT part of my current LOG files, there is a chance that future log files will contain additional columns in the 1st line of each message.
- If so, I don't want to lose 2 columns because I inserted another 2 columns.
- I tried changing line from "ReDim a(1 To UBound(x) + 1, 1 To 8)" to "ReDim a(1 To UBound(x) + 1, 1 To 10)" but I still only output 8 columns (A:H).
So... my question: What line(s) of code would require an adjustment if I had, e.g., 9 columns (vs. currently 7 columns) in the 1st line of each message?
And, finally, is there a way I could specify a header row that would be inserted each time into the worksheet as well? I would define A1="Severity", B1="Date_Time_Stamp",..., H1= "Message".
1000 THANKS FOR THE CONTINUED ASSISTANCE!!!
Cheers,
EEH
Last edited by skydivetom; 10-18-2022 at 03:06 PM.
Sub Test2() Dim iRow, nextRow$, t, iPos%, L(1 To 20000, 1 To 8), R& iRow = Application.GetOpenFilename("Files, *.log; *.txt") '<-- Log or Txt If iRow = False Then Exit Sub Open iRow For Input As #42 Do Until EOF(42) Line Input #42, iRow If Left(iRow, 1) = "[" Then Line Input #42, nextRow: R = 1 + R iRow = Split(iRow): L(R, 1) = iRow(1): iPos = InStrRev(iRow(3), ":") t = CDbl(Right(iRow(3), 3)) / 86400000# + CDbl(CDate(Left(iRow(3), iPos - 1))) L(R, 2) = CDbl(DateSerial(Split(iRow(2), "/")(0), Split(iRow(2), "/")(1), Split(iRow(2), "/")(2))) + t For iPos = 2 To 5: L(R, 1 + iPos) = iRow(iPos + 2): Next L(R, 6) = "'" & L(R, 6): L(R, 7) = iRow(9): L(R, 8) = nextRow End If Loop Close: Cells(1).CurrentRegion.EntireRow.Delete With Range("A1:H1").Resize(R) .Value = L: .Borders.LineStyle = xlContinuous .Columns(2).NumberFormat = "dd/mmm/yy hh:mm:ss.000": .Columns(8).ColumnWidth = 75 .Columns(8).WrapText = True: .Rows.AutoFit: .Columns.Resize(, 7).AutoFit End With End Sub
Last edited by beyond Excel; 10-18-2022 at 05:23 PM.
beyond Excel -- thank you. Please refer to the attachment in post #7. As referenced, the file "testing_additional_columns.log" includes those, e.g., 9 columns. Ideally though, the function would be dynamic to scan through all populated columns in the 1st line. That is, as future message logs may have n additional columns, it would be best to NOT hard code it though.
HOWEVER, as a work-around, I'd be okay to hardcode the # of lines. As indicated in my response to jindon, I changed "ReDim a(1 To UBound(x) + 1, 1 To 8)" to "ReDim a(1 To UBound(x) + 1, 1 To 10)" but the additional columns didn't get parsed in. Maybe I forgot to change something somewhere else.
beyond Excel, jindon -- thank you both for the continued assistance. I have tried both of your versions and I would like to summarize "what is great about it" & "what could be improved". THIS IS NOT MEANT TO BE A CRITIQUE!!
I merely would like to share thoughts for potential adjustments iot achieve my goal to successfully process my log files (with greatest level of flexibility).
What is great about it (in bullet format):
- It outputs the log files w/ the correct # of columns.
- For example, file "ac.log" contains 7 columns (well actually 8 given that "$" is being read as a column).
- Thus, it outputs content into columns A:I (again, where column G only stores "$").
- Alternatively, when executing file "testing_additional_columns.log" (with the 2 additional testing/dummy columns), the content is stored into columns A:K.
Ultimately, the *correct* AND *dynamic* reading/parsing of each message 1st line's column counts (whether 8 or 10) is extremely critical to ensure I won't drop columns for file types that have additional columns.
So, this is fantastic!
Version Issue:
- FYI -- internal to my computer, this Excel version blocks all macros.
- So, I have copied the code into a new version that works on my computer.
- That's perfectly fine (just wanted to share I couldn't use the originally posted version).
- By doing so, I placed VBA code into the individual sheet and linked a command button to it.
What could be improved (in bullet format):
- As indicated in the "What's great about it...", the "$" sign (column G when using file "ac.log") is being read as its own column.
- While I understand that the code merely processes all 'valid' columns, this appears to be the only column that is NOT necessary.
- If there's a way to drop the column that contains the "$" symbol that would be great. Please keep in mind that it shifts from column "G" to "I" when using file "testing_additional_columns.log".
- As part of my playing w/ the versions last night, I added "headers" into row #1. Then, I modified the code to insert data starting in cell A2. That left the headers intact after each run.
- Applying the same principle in this version, however, deletes all content incl. the header row.
- The deletion of the header row is not necessarily a deal breaker but certainly a nice-to-have. Naturally, I may have to have separate XLSM files depending on the column count (one for A:I and another version for A:K log files) if I deal with different header counts.
- So, if there's a way to NOT wipe out row #1, that would be great.
- Also, in a previous version, you included borders... I did like that idea. Preferably, I'd be able to change the RGB code of the border colors though.
- Lastly, I will further play w/ the code to a) throw a message about n lines being processed and b) throw a message box if the file was empty and nothing was processed.
And, finally, would you be willing to add some high-level comments to the VBA?
That would allow me to better understand some of the "key code" (concatenating 2nd line; moving 2nd line into next available cell of 1st line, etc.).
What is great about it (in bullet format):
- It is great that I can process either file type (.log or .txt).
- It does NOT import the column that only stores the "$" character.
- Obviously, as you indicated, the function has completely changed. Allow me to shift to the things are missing now.
What could be improved (in bullet format):
- It appears that the function does NOT process all columns when using file "testing_additional_columns.log".
- That is, the 2 additional dummy/testing columns are not imported. They should be as well.
- Ultimately, my goal is to process all columns (ideally skipping the "$" column). So, whether I have 7 or 9 'valid' columns in the file, all of them should be processed.
- Is thee any chance to add a few additional comments to ensure I better understand the new logic?
jindon, beyondExcel -- I am absolutely thrilled about either of these versions. They already make this process 1000 times better than the non-working text delimitation for this scenario.
At this moment, it's merely a matter of:
1. adding the missing columns (jindon's version)
2. messages boxes to indicate line processing and empty logs (beyond Excel's version)
3. keeping the header row (start in A2 then)
4. wiping out all content except the header row before each run
5. and some basic formatting (e.g., borders, border colors in RGB) ...
... which would make this project even more PERFECT.
Thank you both for the continued assistance.
Cheers,
EEH
Last edited by skydivetom; 10-19-2022 at 06:59 AM.
jindon -- thank you for the follow-up and providing an updated version. This one (post #15), however, does NOT execute. I'm getting a subscript error. See attached JPG.
//
Also, just before seeing jindon's latest version (post #15), I noticed an issue with a ps.log. The screenshots are based on solutions up to **post/thread #13**. See attached .zip file (incl. PPT and ps.log).
Last edited by skydivetom; 10-19-2022 at 07:41 AM.
jindon... wrt to the run-time error, it was my mistake. I had renamed the worksheet. So, it's processing now. I apologize for the confusion.
//
There's a small issue with one of the files. Thus far, all of my sample files include roughly 30 to 50 messages. Only one file (ps.log) includes more than 20k lines/messages. That translated to roughly 3.5k lines/row in the XLSX.
This time, I further scrolled down to the bottom of the XLSX and noticed that some columns were offset. For testing purposes only, I identified those date time stamps and only included a few messages (which resulted in column offset). These are attached in testing file "ps_testing.log". Please see attached PPT for an illustration of the issue.
Thank you for the continued assistance... I very much appreciate it.
P.S. Also, as illustrated in the slides, the "time" column is concatenated with "date" but also included by itself. Is there a way we only include the concatenated value w/o showing time again in column C?
because that line
[ Error 2019/10/24 09:25:28:659 Reader-0 Context $Revision: 1.19.4.4 $ Static Initialization ]
Started
There is a space between Static Initialization, so it expands to next column.
Does this likely to have this position? or if possibly anywhere then need more complex details.
Best response I can give you is "I honestly don't know." Here's why... these are sample files generated by the server. I won't know until later on in the project whether or not I would get files with only this single exception or if there are more.
At this time, I'd like to hope that this case is the only exception. So, if the VBA would be able to recognize this additional space Static Initialization, it would be great.
Would a CASE SELECT be an appropriate solution?
Pseudo Code (using IF/THEN)
If CASE column n = "StaticInitialization" Then
... VBA code to proceed as currently
If CASE column n = "Static Initialization" Then
column n = "Static_Initialization
return to VBA code function
If CASE column n = "Other Unanticipated Value" Then
column n = "Other_Unanticipated_Value"
return to VBA code function
End if/End Select
... so, basically, if I were to run into any unexpected value issue (spaces), I then could keep on adding IF/CASE statement to overwrite these. This is under the assumption that any future log file wouldn't have hundreds of thousands of exceptions. Kinda doubt that would be the case though.
Oh, and yes, I **believe** it would only be the case for this particular position/column. Could be wrong but that's the only field where non-repeated values/list items exist in the current logs.
Last edited by skydivetom; 10-19-2022 at 09:28 AM.
- I totally understand! I wish I had a different answer at this point in time. I guess I will find out in the foreseeable future if I run into some other irregular cases.
- Finally, is there an automated way to remove the time (since it is already included in the date time stamp). I guess I could potentially do brute force (at the end of the routine and delete the entire third column). Just wasn't sure if that's the best method though.
- Once I end up w/ only time being included in the date time stamp, I should be good to go.
//
beyondExcel:
- Wanted to follow up w/ you as well. At some point, one of my files resulted in a run-time error.
- I trimmed down a 25k file to only a few lines.
- Please see attached sample file "ps_post12.log".
- I totally understand! I wish I had a different answer at this point in time. I guess I will find out in the foreseeable future if I run into some other irregular cases.
- Finally, is there an automated way to remove the time (since it is already included in the date time stamp). I guess I could potentially do brute force (at the end of the routine and delete the entire third column). Just wasn't sure if that's the best method though.
- Once I end up w/ only time being included in the date time stamp, I should be good to go.
jindon -- at the risk of repeating myself, please allow me to re-iterate that your solution is PERFECT!!!
- Most importantly, all existing log files are processed (incl. irregular cases as well as the odd ps.log file).
- Also, the testing log file with additional dummy columns recognizes the additions.
- I maintain my header row.
- All data is cleared out after each execution.
W/ that said, I will close this thread for now. In case of additional questions in the future, I hope you won't mind my opening up a new post and cross-referencing to this one.
//
Also, please allow me to acknowledge the work by forum's contributor beyondExcel. Your solution did go in a slightly different direction but with similar results. Ultimately though, the odd ps.log file did not process. I'm sure there's a work-around to it. This doesn't mean your solution is worse/bad... it's merely another approach.
At this time, however, I have an excellent solution to my existing problem. I will go w/ that now. Thank you for your time as well... your work did NOT go unnoticed.
//
So, again, thank you both jindon AND beyondExcel for your valuable contributions. Cheers!!!
jindon -- with your permission, allow me to ask another question.
Background:
- As originally stated, I tried "text delimitation (in Excel) to see what the messages would look like in spreadsheet format. Obviously, they were pretty much garbage.
- Per my initial research, I came across several RegExp examples using MS-Excel. Thus, I chose to open my post in this Excel forum.
Question:
- Again, the VBA function in Excel works brilliantly and I will definitely put it to use.
- At this time, I am curious to see how easy/difficult it would be to convert this VBA routine into VBA syntax suitable for MS-Access.
- I fully understand that this is an Excel forum and this certainly can't be solved here... but it would be really cool if a similar approach could be used in Access... it would eliminate the step to import in Excel and then later on into Access.
I'd welcome your thoughts whether or not this could be done w/o having to complete "re-invent the wheel" (given a database works differently than a spreadsheet).
Short answer:
- It may be 00:25:10:048 (on 10/27).
Long answer:
- The *processing* (analysis) of the log files is a completely new activity.
- Manually scanning (human-in-the-loop so to speak) through the entirety of the data -- and attempting to identify any erroneous data (e.g., dates) -- is I'd say a completely inefficient task.
- Therefore, to fully analyze all data, matrix format is required (in my view).
- Afterwards, once transformed into matrix format, inconsistencies can be more readily identified and discussed w/ the data owners.
All that said, is there a way where your function will accept the data as-is?
Your analysis matches what I imagined.
And since I already have the idea of how to deal with this "oddity" as soon as I have some time I will upload the new version as well as the *.log that the macro resolves.
I very much appreciate the continued assistance. Although I closed the thread earlier today, I certainly welcome any additional feedback/input. I'll respond to your question below:
- When downloading the latest XLSM (as well as previous versions), I cannot run your code in the provided file. I always get the Security Risk error. See slide #1 in the attached PPT.
- So, previously, I copied your code into a new XLSM and created a new command button to link the sub.
- Anyhow, wrt to your questions/comments about the other issues ('Other 2' field), I ran the four Syslog files (0, 1, 2, and 3) via Jindon's routine. See slides 2-5.
What specific issue were you referring to? All 4 syslog files were processed (well, "0" threw the message that's empty), and the "Other2" (logs _1 through _3) field only contain values = "$Revision".
Excellent!!! As part of the analysis (see attached PPTX), here's what I've done.
1. Due to "Security Risk", I copied your VBA into a new XLSM.
2. I ran the four SL files.
3. Also, based on post #24, I ran the ps.log with the additional lines which previously threw a run-time error.
4. I took snapshots of each output (5 .logs).
5. Then, I ran the same .logs with jindon's code and also took snapshots. Copied the JPGs into the same slides for quick comparison purposes.
Please note that I placed the command button (jindon's version into column A... so, in Jindon's version, the data starts in B2 (vs. A2 in your version). That locks down the command button w/o moving it due to increased/decreased columns widths.
Per the comparison/analysis, the outputs of both versions (yours and jindon's appear to be identical). That's awesome!!!
So, I also would like to thank you for spending time to help me come up with a great solution. Once I have new .log files (with potential new irregular values), I will definitely run both of them and continue to assess.
At this time, however, I believe that both solutions provide the same results... FANTASTIC!!!!
Many thanks to you!! Cheers!
Last edited by skydivetom; 10-19-2022 at 06:53 PM.
- Ideally, I would have a form (in Access) that either a) stores the routine or b) calls a module.
- Then, the log files would be directly imported into a table.
- And even better, the routine would loop through all .log files in a subfolder and import content from each .log into a separate table.
So, let's say I had the following:
A. Folder "Syslog Import". It contains the ACCDB + subfolder "Syslog_Files".
B. Subfolder "Syslog_Files" could contain: {Syslog_1.log; Syslog_2.log; Syslog_3.log; Syslog_4.log}
C. Just like in Excel, upon executing the function, I get the Browse_File dialogue box.
D. Ideally, instead of selecting one (1) specific file at a time, the function would loop through all existing files in the subfolder.
E. Following the same exact transformation/parsing routine, it then would create the following table objects: {tbl_Syslog_1; tbl_Syslog_2; tbl_Syslog_3; tbl_Syslog_4}
That's pretty much it... same exact concept except that the transformed data is stored into tables (with "tbl_" prefixes) and that I would be looping through all files. Whether or not a message box (for each successful import) *may* not be necessary.
Finally, I fully understand that this forum is an EXCEL forum. I don't want to be in any violation to ultimately now post an Access question in an Excel forum. If you like, I can open up this question in an Access forum and cross-reference it. What is your preference/recommendation for posting this question?
Many, many, many thanks for the already proposed code (Excel-to-Access syntax conversion).
I created an ACCDB and included the above VBA (in the code section marked "Access"). I did NOT include the code marked as "Excel". Not sure if I should have... 'kinda doubt it.
At the present time, the VBA routine throws a compile error. Again, I'm not sure if additional code is missing or if it's due to the syntax conversion.
Naturally, given that you're the "resident expert" on this particular issue, I'd welcome any feedback in the Access forum. Again, I thank you for your assistance in advance.
1. Yes, I am confident I included all references. See attached JPG.
2. However, I wasn't sure if the Access version requires the VBA code that is listed under "1) Excel VBA" (post #42).
Do you prefer my asking questions in this forum or would it be better to switch over to the Access forum?
Also, I have attached the Access version (incl. the sample log files). Also, attached JPG includes the snapshot of the compile error.
Last edited by skydivetom; 10-20-2022 at 05:36 AM.
I just saw that the .log files are converted in another version of .log files (in the same directory).
Ok, that's different... I had hoped they would be directly imported into table objects in the ACCDB file. Can that be easily modified?
// UPDATE //
I have noticed the following:
- When I click on the ACCDB's command button, the browse dialogue box pops up twice.
- It appears that only the 2nd click actually creates the .log files with the "_Pipe" extension.
Next:
- Again, I had hoped that -- instead of creating -- another set of log files (even though these are much cleaner!!!), that the data is directly stored into Access tables.
- Either way, for testing purposes, I tried to now import the piped .log files into ACCDB.
- When opening the "external data", I then chose "From File" | "Textfile".
- Upon browsing to the directory where the piped .log files are stored, Access only display "Text files".
Although I'd still prefer the direct import into tables (vs. temporary .log files). However, if that's not doable, could the piped files have a .txt extension (vs. .log extension)?
Last edited by skydivetom; 10-20-2022 at 06:06 AM.
Summary:
First of all, this process is exactly what I needed!! This is yet another BRILLIANT solution of yours.
With your permission, allow me to follow up w/ a few questions:
a. Importing of Syslogs does NOT occur when clicking the command button. Only the "2nd click" of it, imports the file.
b. Below is a recap of the exact procedure.
- Click on command button "Import Syslogs"
- By default, browse dialogue box shows "Documents"
- I navigate to the subfolder containing log files
- I double-click on subfolder "Syslog_Files"... it open but won't display anything (that's ok).
- I click "OK"... nothing happens
- I click on commadn button "Import Syslogs" a 2nd time
- This time around, browse dialogue box immediately display the logs' subfolder
- I click on "OK" again
- At this point, the 3 logs are imported into Access.
Follow-up questions:
1. Is there a way that the the "1st click" (selection of subfolder) imports the log into tables?
2. What's the difference between 1st and 2nd click of "Ok" where 2nd click does import the files?
3. If I were to replace the .log files with another version, will the old tables be dropped first and then re-imported w/ new data?
4. And lastly, is it doable to strip off the "_pipe" extension from the table names and, instead, insert a prefix of "tbl_"? So, instead of displaying "ac_pipe", the table would be named "tbl_ac".
Even if the above cannot be accomplished/solved in this thread, THIS SOLUTION IS HUGE!!!
In fact, I probably should be deleting my post in the Access forum. 100,000 thanks!
Thank you for the continued assistance and patience with me. You've gone way beyond the help that I would have ever expected.
I assure you though, your help is GREATLY, GREATLY appreciated.
That latest version is fantastic!! I really only have a very few follow-up questions. I'll summarize in bullet format.
A. 1st click vs. 2nd click "OK" on dialogue box (once I click on command button):
---------------------------------------------------------------------------------
1a. For some reason, I still have to click on the command button twice before I see the tables being imported.
1b. Do I actually have to open the subfolder and then click "Ok" OR
1c. Must I only select the subfolder (w/o opening it) and then click "OK"? I tested both scenarios and I don't think one works over the other. It still requires a 2nd click. Not a deal breaker though.
B. Message Box
--------------
2a. I am an absolutely fan of message boxes for user feedback.
2b. The Excel version threw a message box after import into the worksheet. That was fantastic... particularly when loading the actual log files since some of them have hundreds/thousands of messages.
2c. Now, in the Access version, I would NOT want to interrupt the looping by always having to click a message box for each log file.
2d. However, is there a way that the n values for each log file could be included in a message box after the last log file was imported?
2e. In the attached ACCDB version, I have included a *hard-coded* message box.
2f. So, is it possible to change the hard-coded message box to a dynamic message box wrt to the record count (integer)? Again, see attached PPTX for details.
C. Comments
-----------
3a. Although not critical, would you be willing to add a few high-level comments into the VBA? Only for the critical lines (e.g., delimitation, concatenation, moving line up, etc.).
In summary, the inclusion of the message box would make this project 100% complete (I think).
Thank you,
EEH
// UPDATED...
Oh, and finally, the "Date time stamp" gets currently stored as text in the table. What's the appropriate date format when including "miliseconds" in the table?
Last edited by skydivetom; 10-20-2022 at 02:32 PM.
This is a follow-up to my post #52. I have included a custom message box.
Pros:
- It shows the correct number of records for all imported tables.
- It shows the processing time.
Cons:
- I will have to manually maintain the filenames in the declaration section prior to the message box.
- At this time, I foresee that it *may* not be a problem... the assumption is that I have fewer than 10 files.
- Naturally, if that assumption is incorrect and I'll end up with various different file during each data dump, the manual referencing is not ideal.
For now, however, I think this should work.
At this time, this leaves only one (1) thing that I still need to solve. That is, presently the date time stamp (date/time + milliseconds) is imported as data type = "text". The DTS is a crucial field on which I will have to make some form of calculations.
How would the code have to be modified so that the date-time-stamp will be stored as a "date"?
Thank you for the feedback. I wish to extend my sincerest apologies... last thing I wanted was to abuse your help. Again, I'm sorry if I my continued question offended you.
As recommended I will continue the research in an Access forum.
Again, thank you for all of the assistance you provided me in this thread.
All of the imported fields are of string length = 80. I can't get rid of the trailing characters (illustrated as "black spaces") in JPG posted in Access post #12.
Is there anything in the Access import routine that would prevent adding all these characters? Right now, every string value across each field = 80 characters in the syslog tables.
Sorry to keep bugging you but many Access expert have chimed in and there's still no solution.
I don't know why you asked this in the Excel forum in the first place.
You should ask in Access forum as you asked in this thread and do it all in the Access from the scrutch.
OR, contact professional Access programer near by.
I don't think it is not possible for you to even maintain DB...
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by skydivetom
Bookmarks