• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Import certain data from text file into a Excel worksheet

pete_agreatguy

New Member
Hi all,

I would like to find a way of automating "getting" certain data from a log.txt file as doing this manually via notepad++ is extremely tedious - let alone time consuming.

Any help would be highly appreciated in getting this automated at the click of a button within Excel, assuming it is feasible?

Please note that even though I have had some programming experience in the past (2003) I am not so confident in myself so please treat me as such. I also suffer mental health and my concentration is poor at the moment. i.e. Please be kind. Thank you.

The data I would like to find within a text file to transfer into an Excel worksheet using VBA:

Files attached:
  • output_log.txt = Contains the data

  • results.xlsm = Data retrieved manually by using the "search" feature in notepad++ (i.e. the end result I would like to have)

  • vba_help.jpg (g drive link) = Please refer to this when reading the description below as it will aid your understanding considerably
As you can see in the screenshot, in excel I have a table that includes:
  1. Flight No = an aircraft's flight number
  2. Type = if it is a departure aircraft or an arrival aircraft
  3. Time = the time this aircraft landed (or took off) successfully
  4. Runway = the runway that the aircraft landed or took off from
  5. Entry Point = the taxiway that an aircraft used to enter the runway (dep. only)
For example (how I got this data manually) - please refer to vba_help.jpg:
  1. The flight no (consider this to be the unique identifier) can be found on every single line of code mentioned below

  2. To find out "Type" and "Time" we can look for these specific statements:

    a) Dep. aircraft = e.g. line 9510 " from STATE_TAKEOFFUP to STATE_FLYAWAY"
    -- i.e. the log file is telling us that this aircraft (NKS906) took off successfully at 11:55:25

    b) Arr. aircraft = e.g. line 9572 " to STATE_ESCAPE_RUNWAY"
    -- i.e. the log file is telling us that this aircraft (FFT1577) landed successfully at 11:56:37

  3. I've covered Time in step 2 above

  4. The runway can be obtained from the same line as the successful landing/takeoff line. Or we can obtain this from the last instance of the "Entry Point" in the log file (see below).

  5. The entry point is only applicable to departure aircraft. There can be more than one instance of this. However; that said, it is always the last instance of this string in the log file which is the correct data that is required.

    e.g. line 9530 "Route is: GA_term_1, H, F, D, calculate time: 0.002258301s highest count: 1278"

    It is the last letter that is required (just before "calculate time") - highlighted as blue text above.

    An example in this log file of one departure having more than one instance of this "entry point" would be SWA3029:

    line 9391, it's route is "Route is: Term_C7, B4, C, B, calculate time: ...."
    ... whereas later, on line 9426...
    line 9426 it's route changes to "Route is: B4, B, A2, calculate time: ..."
VBA Code:

From some research (as I don't really know what I am doing), I've established I need to create a command button on 1 sheet. Then assign a macro to it which will be the VBA script.

So something like the below:
Code:
Private Sub getresultsButton_Click()
Dim ... variables As Integer / String (where required)

Some kind of loop???
If statements???
End If statements
End Loop
End Sub
Thank you in advance for any help as I really do appreciate it :)
 

Attachments

  • output_log.txt
    834.7 KB · Views: 4
  • results.xlsm
    12.6 KB · Views: 3
pete_agreatguy
Flight, Type & Time
... something like this?
Gotta figure the rest ... maybe later

Check [tmp]-sheet and press [Test]-button

Yup - Looking great so far!

I'll have a look at the code you did also as I can maybe able to understand some of it.

Huge thank you for your aid. :DD

Do you understand what I mean with the last data required - in regards to the "entry point" for a departure?
 
Last edited:
pete_agreatguy
I'll do these 'My Way'...
You'll test and verify ... 'Your Way'.
I meant look at the code to see if I could learn something to clarify not to code it myself as I feel incapable of doing this myself.

Looking really great so far - again huge thank you :)

For some reason it is missing only a few "entry point" results.

In the output log attached above (link) it misses ROU1861.

In the output log attached below it misses UAL451 and AIJ979.

ps: I'd like to make a donation to you for your aid - do you have Paypal? I can send a payment via Paypal if you have an account. If not, is there some other way to send a payment to show my appreciation though I would prefer a paypal payment.
 

Attachments

  • output_log.txt
    836.5 KB · Views: 2
pete_agreatguy
I quick compared those my results with Yours
... there could be differences
... that's why I wrote 'You'll test and verify ... 'Your Way'.
I'll check those Your named 'flights' soon...
(( Even this is not like 1+2=? Yes, 3!
There could be many other numbers than 3 to try to give as the result.))

Case 'a donation'...
There is a link for that case ... snapshot from end of my previous reply.
Screenshot 2019-03-20 at 12.40.56.png
... turn Your eyes more right and You'll see
want to donate-link.
Thank You.
 
You are correct; each output_log.txt created will have different results every time :)

So for example, in the 1st log file ... ROU1861

The aircrafts 1st route is on line 12767:
12:51:05 alt: 0 takeoff: False/False/-1 OWNER_GROUND r: 26R * ROU1861 => Route is: Term_D24, C3, C1, C, B, calculate time: 0.0009765625s highest count: 292
But the last instance of this route (the entry point required) is actually on line 12870:
12:52:57 alt: 0 takeoff: False/False/-1 OWNER_GROUND r: 26R * ROU1861 => Route is: C3, C1, Q, R, B, A2, calculate time: 0.4985352s highest count: 98984

The important bit in the log file string here is the " => Route is" as this is the taxi route an aircraft will take.

In the 2nd output log file I attached the ROU1861 "entry point" appears in the results correctly.

i.e.
The aircrafts 1st route is on line 12890:
12:51:15 alt: 0 takeoff: False/False/-1 OWNER_GROUND r: 26R * ROU1861 => Route is: Term_D54, C5, C3, C1, C, B, calculate time: 0.0009765625s highest count: 371

The last instance is on line 12992:
12:52:40 alt: 0 takeoff: False/False/-1 OWNER_GROUND r: 26R * ROU1861 => Route is: C5, C3, C, R, B, A2, calculate time: 0.4599609s highest count: 91006

Not sure if this helps your troubleshooting?
 
If it helps ... this bit of string from the log file only appears for departure aircraft:

The important bit in the log file string here is the " => Route is" as this is the taxi route an aircraft will take.
 
Back
Top