Format Text file for Excel

Not sure why you want to use Excel as an intermediate for this problem. Any macro to load the data into Excel would be very similar to the one in Access. If you need an Excel file, you could export the table out of Access into Excel.

Blane 245,

Ok, I understand what you are saying, export the table out of Access, but have you looked at the format the text file is in, it is not a straight copy and paste into a table. Can you assist me with this?

James
(ex-aussie from Brisbane)

Sorry, don't have an ACARS parser handy. Looks like a straightforward parsing problem, though. You might check web sites like http://www.acarsd.org/ to see what they have to offer.

Blane245,

Thanks for the reply, I have done that and there is nothing available, unless I buy a Log Analyser program. I just wish to use Access to make my own log analyser.
I am not too good in VBA, so looking for assistance.

Thanks

James

I can guide you through this, but I need to know how much experience you have with VBA before we get started. Have you written any VBA macros?

Blane 245,

I have done a little VBA, still in the beginner steps, though I am very willing to learn.
Where do we start and what information do you require from me?

James

First, which of the ACARS keywords are you interested in? The ones that I see in your sample file are
ACARS Mode
Aircraft reg
Aircraft type
Message Label
Message Number
Flight Number
Operator
Message Content

Are you also interested in extracting the date/time stamp on each message?

Blane 245,

Yes, you have it right, the date stamp and time is needed. If you look at the bottom of the attached text file you will see the desired output I wish and fields needed as well.

Thanks for your assistance!!

OK. so let's get started. Attached is a workbook that is the start of the file parser. All I have now is the code to open the file and create a new worksheet in the current workbook. See if you can follow it.

The workbook has a Notes sheet that contains some info on how the parser will work.

It also has a Run Sheet worksheet that hold the button to start the execution and the name of the file to parse.

Let me know if you're with me on this. Do I need to explain anything further?

ACARS Parser.xls

Blane245,

Ok, so far I am with you. The only thing I see at the moment is to change the folder loaction to suit my environment, but this is minor.

OK, please proceed!!

Great! The parser will take a little longer. Here's the next cut. You will see the skeleton of the parser. The code should run without problem, reading the entire file, but not doing anything with it.

Let me know if you need any clarification.
ACARS Parser.xls

Blane245,

Great Job, I really appreciate this effort!!

In this poart of the code:

Dim r As Long ' the current row
Dim i As Integer ' keyword index

r = 1
With wSheet
' write the headers onto the first rwo of the sheet
.Cells(r, 1).Value = "Date"
.Cells(r, 2).Value = "Time"
For i = 0 To UBound(Keywords, 1)
.Cells(r, 3 + i) = Keywords(i)
Next i

Ok R is setting up the row starting at 1, but what is UBound?

Thanks

Shg,

Thanks for the heads up. I will watch this next time.

Your in Texas, where abouts?
I am in Austin!

Thanks

James

I can answer that question, but you can get the answer from the online help. Put your cursor in the UBound word and press F1. See what happens. Great feature of VBA.

I am almost thru with the parser. Please watch the forum rules so we don't get shut down.

Blane245,

Thanks, that is a great feature, I am going to find that useful.

Yes, I will need to watch the rules, I just plain forgot about the code tags.

Thanks, wow you are very quick at VBA.

OK, bigroo, here's the whole thing. I have run it for you so you can see what it outputs. the only thing that I didn't do was to make the column and row formats pretty. You said you were going to send this to Access, so that isn't a big deal.

I did one thing that you may want to change - I removed all of the blank lines from the messages. If they are significant, you can add them back in.

Enjoy

BTW, I spent some of my life in Texas (Houston, Tyler, Liberty). I miss it sometimes.ACARS Parser.xls

Blane 245,

Thanks so much, this is what I want.

One thing, the date is not in US format, can we reverse this, this was last Wednesday's file run.

You lived in Texas, I guess you miss the warm weather then!!

Thanks

Here ya go.ACARS Parser.xls

Blane245,

Thanks for your efforts, everything looks good and is exactly as asked for.

I really need to spend more time on VBA to get a handle on this language, I am finding many opporunities I can use code to do simply tasks.

Thanks so much and I look forward to tapping into your knowledge further.

James Niven
Austin, Texas

Spend a little time with this macro as a learning tool and don't forget to close this thread.