Anyone know their way around EXCEL and VBA MAcros?

[stilly]

Yeah so today we received a request to pull the arrival times that a certain train (M204) arrived in LAUS for the past year.

Believe it or not, I guess nobody has ever asked that before. All of the tools at our disposal failed. They could not even produce a single DAY.

So I had the brilliant idea to take Excel, make a command button and I put in a macro that should look through several hundred log files and display every date and the time that that train was at a certain milepost.

Cept it does not do that...

Currently I have a macro that will search through all of the files and pull out a string of text and sub strings I guess, but damn. Each line has a lot of data/garbage that I do not need. (which I use the INSTR command to pull out substrings)

I am looking to have Excel look through about 600+ log files that are anywhere from 50-300MB large and pull out every time the train 204 pulled into the station. There is a certain string of text that the log files display showing the mile post for the beginning and end of the train. I just need Cell A2 to show the date, B2 to show the time and C2 to show the milepost it was at at that time.

The log files are TXT files that are essentially lines of code that you can read and pick out certain events and all, but there is no CSV or spaces, it is all spaced out or ran together.

So I guess I want the macro to look at each log file, find the piece of the code that shows M204 AND the milepost (xxx.xxxx) and then put it into a workbook that I can save and forward to the higher ups.

Where would I go to read up on such a thing? So far I got my macro to parse through the files and pull the FIRST instance on seeing M204, but the train could be listed several hundred times AND in some cases, it could also NOT be listed (M204 does not run on the weekends). So there can be no errors generated due to a log file that does not list M204.

My vba skills are weak. I think I learned most of the puzzle tonight but damn, I would sure love to be able to complete it.

Stack overflow has some certain pages I saw, but I had to keep looking.

Is there a good bible or cook book for Excel 2016?

Any help here is appreciated. I am looking for EXCEL VBA resources and stuff. I am not very familiar with the commands. Time to go get an e-book or two I think.

Thanks for your time if you are still awake.

[Marauder2003]

Are you familiar with Experts Exchange? They have a free option. Still have to join. They have sections for all the Office products.

https://www.experts-exchange.com/plansAndPricing.jsp

[ElDub1950]

So, your macro already works to open each file and scan through the text lines to pick out the lines related to M204? And you're just having trouble parsing the data out of the selected lines? Reading in multiple files and selecting specific lines is usually the harder part.

If that's the case, then you just have to tweak your Instr() and Mid() statements for each cell.

Your Instr() will identify the starting character position, then your Mid(start,length) statement uses the start position, length of data.

This of course requires that each line in the log contain the same data in the same format. There at least has to be some standardization of how the data is written. For example:

M204 blah blah blah Date 04/01/2017 4:33 PM blah blah blah Milestone 874

You Instr("Date") gives you the start.
Then your date field is Mid((Instr("Date") +5),10).

What gets messy of course is if 04/01/2017 is randomly written 4/1/17. If this happens, then you have to use multiple Instr()s searching for the start position then another Instr() to find the slashes. Becomes a very very long calculation, but doable.

Difficult to get much more detailed without working with the data file.

As far as references, I typically just google the concept you want ( Microsoft Excel VBA parse text line ). I've bought a couple of books in the past but never use them.

I'd do this this kind of thing in 2 steps. First run your macro, selecting the M204 lines and writing them to a new file. Then your next steps are run on the new file containing only the data lines you want. So you only have to process the 600 log files once.

Personally I find doing this kind of file processing easier in MS Access VBA, but that's only because I've been using it for 20 years. It's almost the same though.

[ocabj]

Light up Elasticsearch. Feed the data into that. Use Kibana to query the data.

https://www.elastic.co/products

It is free and you should be able to light up an Elastic stack in a few hours.

Elasticsearch is a great way to process structured and unstructured data.

[TacFan]

AutoIT

[SkyHawk]

Let me see one line from a log file (post it here), the one you care about. Let me know what is unqiue in that line that you are searching for, and show what pieces of info from that line you want in cells.

I personally would use a DOS find script to search the logs, output the relevant lines to a temp file, then manipulate that file using any number of DOS tools (like ALTER) to make it a comma or tab delinted file that could be simply opened in Excel.

[stilly]

Alright folks. I appreciate the time yall have spent here and there is some good info. I am going to have a look at those Elastic products. They seem like they are decent tools. I hope they are free, if they cost money I would have to pitch it to the sup.

Line of code- Hmmmmmmm. Yeah I will have to grab one when I am at work next. Today is my Saturday. I can fulfil that request late Friday night. I will return with a sample of code.

In regards to finding my macro/coding, it is hard to do it exactly, but I was doing just that with Google.

I ended up googling my way to finding code for parsing through one file, then through multiple files, but the person in there was only looking for one line of text in an instance of one.

I am looking for the last line of text, Well, I WAS, but then I realized that if the macro has to search through a 283MB file and the text it needs is right up in the top 1/5th of the file, why tell it to search to EOF ?

SO I decided that I need to search for a SPECIFIC line of text, and thankfully, that line is very consistent. It starts with the date, then the time because it is generated in the server and every line has a date/time stamp on it, then the specific message but the problem is that for each milepost segment the train gets to, there are 5 or 4 messages generated, each line is identical, except that they may have a slightly different time stamp, but then, each message is generated for all workstations, and each line says WS1 to WS5. I believe this message gets sent to all of the dispatchers/workstations so hmm. I do not know why the Orange dispatcher gets that message and I do not know why the San Gabriel dispatcher gets it, but they appear to get it, or SOMETHING is going on because their WS is mentioned.

So Anyways, I decided that I need the line of text to match certain criteria. That criteria is that the line needs to have M204 (the full name would be something like "M204 8" but it could also be "M204 3" etc) Then it needs to have the milepost segment which I believe was 00.3xxx and then from that criteria it needed to pluck certain characters from that line: I had it grabbing the first 10 characters, and then the second 10 or so to give me the date and time, and then I needed that milepost so that I could look at the sheet and see that the date and time and the milepost so it can be verified.

At this time though, I also want to add the train ID, full train ID and the leading engine. I figure that if I am going to have a macro here to do this, then I want to grab several more items and then later on I can just trim it out of the report.

This is no longer a mission critical project. Turned out one of the PTC engineers had a tool that did something similar to this, but it generated a text file that had a lot of stuff in it and last night I spent about an hour formatting an excel file but it only had all of the 2016 runs. Plus there were duplicates and triplicates of the data so I had to weed through it still. And on top of that, it did not just have the date and time like I was looking for, it had those attached to the location and some additional data so I had to manually delete out text for about 216 or so train runs.

I do not know much about the elastic products, they look like they could be very useful. I will go have a look. BUT, I also want to know a little more about these VBA scripts and tools for making macros. I think that if I learned a bit more of this stuff and became a semi-quasi coder, that I could make and assist with several more tools and further secure my spot as a NS on the team, then we could have a spot open up for another OA and I could bring in another calgunner.

As for experts exchange, I am not a fan of them. Something about looking for the answers and I see they hide things from you unless you have an account. I do not like data mining and when I am casually looking for an answer, having to make an account just to see an answer bugs me. I have not ruled them out completely though although I also do NOT want to be spoonfed the complete answer. Just bits so that I can see how it works and plug things in myself. I also want to modify the code to search through other things and produce results for all trains, other trains and specific locations.

I am not sure about autoIT, but I will search it out and see what it is about as well.

Thank you all for your time so far, I do appreciate it. I will return in a couple of days with about 10-50 lines of code.

[ocabj]

I just realized you said you need to process log files. I thought you were trying to process an Excel spreadsheet. I didn't realize you were taking log files to put them into Excel.

Why can you just grep the logfiles?

This is standard stuff you would do on a central syslog server, for example if you were trying to look for a specific username logging into a host, or perhaps looking for a specific MAC address in DHCP lease logs.

If this is a standard syslog style file, or a single line log entry style of file, then shoving that into Excel and trying to query the logfile just doesn't make sense at all. You already have the logs in a parseable, searchable format.

So I guess I want the macro to look at each log file, find the piece of the code that shows M204 AND the milepost (xxx.xxxx) and then put it into a workbook that I can save and forward to the higher ups.

grep 'M204' logfile | grep 'xxx.xxxx'

would return all of the entries with "M204" and that milepost string.

300MB log files are nothing. The bro conn log files for our border traffic are usually 1.5GB (gzipped) and these are hourly. We always grep the bro conn logs when we confirm security incidents (which are multiple times per day) and it doesn't take very long to parse them.

But we feed bro logs into elasticsearch as well just so we have a better way to quickly run through logs over multiple days and to more complex correlation (e.g. look for any network flow with source or dest of IP 1.2.3.4 and with a corresponding source or dest IP in cidr 4.3.2.0/23).

[tacj]

You could search the log file line by line to EOF for the value you need, but terminating the search when a match is found.

Once you have the line containing the value you searched for, you could then use regex to extract whatever data you needed from that specific log entry.

Shoot over 10-15 sample lines of the log and examples of the data you'd like to extract and I could write up a class for you.

[stilly]

The Line of code that I was looking for was this:

"01-05-2016 07: 22:29.822 DATAGRAM FORWARDED TO CLIENT: <DOC-TMDS-WS5><113> [VETMS;INFORMATION;LOCO-STATE-CHANGE;ACTIVE|M204 05|SCAX 646|1/5/2016 7: 22:29 AM|DECREASING MILEPOSTS|0.177||0.2548||N|2000|12|DISENGAGED|"

In this case, the information needed is M204, but the criteria needs to be that the numbers after MILEPOSTS need to be below .5, I think DECREASING needs to be in there as well. In this particular instance I believe that when they hit milepost .5 or less then they are considered to have arrived at this location.

Log files contain thousands of lines of code, but this line is what I am looking for, it is either in the file or it is not, but the lines are always the same in this case, there are other lines of course, but they are irrelevant in this case.

Yes, Terminating once the string is found is good because this line will typically be towards the beginning of the log file and they will be several hundred MB large sometimes.

I think it would be neat too if I could have it pull out the engineer's name, the SCAX ID and the Train and date for another project.

[TacFan]

Easy. I'll have an executable for you this weekend. Pm me your email address or location to upload

[Tripper]

I would use TexTools, to comb the files, extracting only the lines i need, once done, loop it again to parse the data from those results into a new csv file, with the fields I want, which then you can do whatever you want with


Sent from my iPhone using Tapatalk

[Satex]

What you need is a python script.