Importing complex Text

  • Thread starter Thread starter Alexes
  • Start date Start date
A

Alexes

I am trying to import a coded message into a table that I can
manipulate in Access 2002. The message comes in the body of an email
and looks like this:

ACTIVITY LOG
AOCDX/PP/R2/D2//
RMKS/THIS IS A REMARK//
COMTECH/CASE/123.567MHZ//

AOCDX/OO/R1/D1//
OPSUM/CODE/CODE/AIR ACTIVITY//
COMMENT/THIS IS JUST A COMMENT//
RMKS/THIS IS A REMARK//

AOCDX/OO/R6/D6//
OPSUM/CODE/CODE/AIR ACTIVITY//
COMMENT/THIS IS JUST A COMMENT//
RMKS/THIS IS A REMARK//
LOC/112233N 224455E/223344S 445566W//

I have tried for the last two weeks to find a way to get this into an
access table that uses each UNIQUE coded field (data between the
slashes (/) as a column. The example above would need 18 different
columns. I am able to import using TransferText with an import spec by
replacing the double slashes at the end of each line prior to import
but the table looks exactly like the message without the slashes. Is
there a way to parse field-to-field automatically using queries,
recodsets, macros or am I stuck? There is no way to have the message
sent in a simpler format. Any references or examples (I'm a newbie)
greatly appreciated. Thanks.

Alexes
 
Try it this way:
Copy and save the data as a .txt file.
Import into Access as a Fixed Width Text file.
On the Second screen of the Import Text Wizard you can set
wherever you want the fields to be set up using Break
Lines in the Wizard.
You will need to "clean up" the // and / before or after
the import.
 
Hi Alexes,

This should be possible one way or another, but I can't work out where
the 18 columns begin and end. Can you post back showing exactly what
should go in which field? Also, does every record always have the same
18 fields in the same order? If not, what are the rules?
 
OK-
Each record begins with "AOCDX."
Each record will have the same sequence but, may not contain all
feilds.
Some records can contain upto 18 feilds while others may have as few
as 5.
A smaple record using the first line of data:
PP,R2,D2,THIS IS A REMARK,123.567MHZ
For 5 columns total.
Also, the first word of every line indicates what type of information
follows on that line. And each line of "related" infomation ends with
double slashes (//).
Sorry for getting back sooner - I appreciate all your help.

Thanks...
 
OK-
Each record begins with "AOCDX."
Each record will have the same sequence but, may not contain all
feilds.
Some records can contain upto 18 feilds while others may have as few
as 5.
A smaple record using the first line of data:
PP,R2,D2,THIS IS A REMARK,123.567MHZ
For 5 columns total.
Also, the first word of every line indicates what type of information
follows on that line. And each line of "related" infomation ends with
double slashes (//).
Sorry for getting back sooner - I appreciate all your help.

Thanks...
 
Alexes,

Unless one really understands the structure of the data it's not
possible to import it reliably. I still don't quite see how you get from
this (in your first message)
ACTIVITY LOG
AOCDX/PP/R2/D2//
RMKS/THIS IS A REMARK//
COMTECH/CASE/123.567MHZ//

to this
PP,R2,D2,THIS IS A REMARK,123.567MHZ

in your most recent one. Some specific questions:

1) Does the "ACTIVITY LOG" line always mark the beginning of the data?
If so, what marks the end of the data?

2) You say the first word of every line indicates what type of
information follows on that line. Does this mean that the structure of
each line depends on the first item on the line and that (inferring from
your sample)
- an AOCDX line always contains exactly 3 fields of data
- a RMKS line contains exactly one field
- a COMMENTS line contains exactly one comment
- a COMTECH line contains exactly two elements
- a LOC line contains two (and only two) latitude/longitude pairs

3) Why does the item "CASE" from the COMTECH line not show up in your
sample result?

4) Please explain the usage of // at line ends more clearly. From the
sample you've posted it appears that every line of data ends with // and
that records are separated by an empty line.
-What would it mean if a line ended with a single / ?
-What would it mean if a line containing data ended with just a line
break (neither / nor //)?

5) What are the other 13 possible fields?

6) Is it possible for the data to contain
- line breaks?
- commas?
- quote marks ""?
- slashes / (and if so, how are they encoded)?
- backslashes?
 
John,

Thanks for the quick reply! In answer to your questions:
1. AOCDX marks the beginning of each record. There is no end marker.
Can't rely on "Activity Log".
2. Yes!
3. My mistake, CASE should have showed up. Sorry.
4. Within each record (between one AOCDX and the next) there are
several possible categories(for lack of a better word) distinguished
by the first word on each line. The format within each individual line
is standard as you inferred from question #2. Each category always
ends with // but not necessarily on the same line. So if a line ended
with / or " " it would mean that it is not the end of the category.
5. Other possible fields have the exact same structure and should work
identically.
6. The data can contain "'" "\" " "but slash (/) marks (separates)
data within a category.

Thank you for your help!

Alexes
 
Things are becoming clearer.

The general approach would be something like this:

1) Create the table you're going to import into and open a recordset RST
on it.

2) Read the file line by line dumping everything until you find a line
beginning with "AOCDX/" .

3) Read the file line by line into a string variable strRecord until the
next line beginning with "AOCDX/" .

4) Split strRecord on "//" followed by a line break, into an array of
strings. In VBA, something like
Dim arRecord As Variant
arRecord = Split(strRecord, "//" & vbCRLF)
This puts each item (usually a line) from the input file into a single
string.

5) For each element of arRecord, replace "/" & vbCRLF with plain "/" to
get rid of any line breaks that are part of the input file layout.

6) For each element of arRecord,
Split on "/" into another array arItem
Select Case arItem(0) 'contains the first word so tells us
what sort of item it is
Case "AOCDX"
'There should be 3 more elements of arItem, corresponding
to the 3 data elements on an AOCDX line; put them
into the appropriate fields:
RST.Fields(0).Value = arItem(1)
RST.Fields(1).Value = arItem(2)
Rst.Fields(2).Value...
Case "RMKS"
'just the one data element
RST.Fields(3).Value = arItem(1)
Case "COMMENTS"
'and so on...
End Select
 
Back
Top