Thread: 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