Importing complex text file into access table

A

Alexus

I'm designing my first database using MS Access 2002(XP).
I cannot figure out the best way to transfer (/)
delimited text from an outlook email to a table in
access. Here is the background:

I receive a daily coded email message that contains many
records of pertinent information that I want to store in
my database for historical and trends analysis. The
message looks like this (abbreviated):

(MESSAGE 1)
AOCDX/PP/TS/312 1756Z//
OPSID/DTR//12345/NOTHING HERE/
ACTI/12/ROUND ABOUT INFORMATION/
COMTECT/123.987MHZ/W9ASG3546/KJDFKAJSDFKD/
RMKS/ THIS IS JUST A REMARK/
LOC/11232E 112344S/
DATETIME/ 1234Z MAR 03//

(MESSAGE 2)
AOCDX/PP/TS/312 1758Z//
OPSID/AFR/56789/SOMETHING HERE/
COMTECT/1234HZ/WRWE987R87W/SDFKJHSADJHSA/
RMKS/THIS IS JUST A REMARK/
CMNT/THIS IS JUST A COMMENT/
LOC/11232E 112344N/
TRK/11232E 112344N/11232E 11245N/11232E 11246N//
DATETIME/1234Z APR 03//

And so forth... (I hope you get the idea - every message
is not identical)

Additional information:

1. Every MESSAGE starts with "AOCDX".
2. The first word of each sentence is a code and is
followed by its related information.
3. Every MESSAGE has the same "POSSIBLE" codes but they
are only used when needed.
4. Every MESSAGE has a unique alpha numeric code
in "DATETIME."
5. (/) is the delimiter and each line will always have
the same number of (/)'s. Example from (MESSAGE 1) above:
AOCDX//CD/312 1756Z// There are two blank field in this
record.

How in the world do I get this into an access table? I am
currently doing the whole process manually and daily.

I wrote a specification and successfully imported but the
table is acattered all over the place. How do I get all
the right feilds in the right place?. Please help. Any
information would be greatly appreciated. Thank you.

Alex
 
M

Mike Painter

Alexus said:
I'm designing my first database using MS Access 2002(XP).
I cannot figure out the best way to transfer (/)
delimited text from an outlook email to a table in
access. Here is the background:

I receive a daily coded email message that contains many
records of pertinent information that I want to store in
my database for historical and trends analysis. The
message looks like this (abbreviated):

(MESSAGE 1)
AOCDX/PP/TS/312 1756Z//
OPSID/DTR//12345/NOTHING HERE/
ACTI/12/ROUND ABOUT INFORMATION/
COMTECT/123.987MHZ/W9ASG3546/KJDFKAJSDFKD/
RMKS/ THIS IS JUST A REMARK/
LOC/11232E 112344S/
DATETIME/ 1234Z MAR 03//

(MESSAGE 2)
AOCDX/PP/TS/312 1758Z//
OPSID/AFR/56789/SOMETHING HERE/
COMTECT/1234HZ/WRWE987R87W/SDFKJHSADJHSA/
RMKS/THIS IS JUST A REMARK/
CMNT/THIS IS JUST A COMMENT/
LOC/11232E 112344N/
TRK/11232E 112344N/11232E 11245N/11232E 11246N//
DATETIME/1234Z APR 03//

And so forth... (I hope you get the idea - every message
is not identical)

Additional information:

1. Every MESSAGE starts with "AOCDX".
2. The first word of each sentence is a code and is
followed by its related information.
3. Every MESSAGE has the same "POSSIBLE" codes but they
are only used when needed.
4. Every MESSAGE has a unique alpha numeric code
in "DATETIME."
5. (/) is the delimiter and each line will always have
the same number of (/)'s. Example from (MESSAGE 1) above:
AOCDX//CD/312 1756Z// There are two blank field in this
record.

How in the world do I get this into an access table? I am
currently doing the whole process manually and daily.

I wrote a specification and successfully imported but the
table is acattered all over the place. How do I get all
the right feilds in the right place?. Please help. Any
information would be greatly appreciated. Thank you.
Click to expand...
I usually bring the whole textfile in as an access table with just two
fields, an autonumber key and each line of text.
Then I run queries to get rid of blank lines and other garbage as needed.
From there some coding is required to parse out the text.
It's not hard if you have some programming skills but can be a lot of busy
work.
 
Top