Ex:

line 1 - header (need some info from here)
line 2 - record1 line1  (need some other info)
line 3 - record1 line2  (need a couple of characters)
line 4 - record1 line3  ...
line 5 - record2 line1 ...

and the lines don't break down cleanly in order to put them into a table
(I can't just do an import because there's no clean break between fields
between the individual lines in the record).  I'm trying to put them
into a table into a couple of big fields for each line and then set a
string variable to the field manipulate the data from there, but it
looks like Access doesn't have a lot of power to identify patterns in
strings, cut specicific areas from strings, etc.

Anybody have any suggestions, or should I do the manipulations in UNIX
and then import it cleanly?

Thanks!

--== Sent via Deja.com http://www.*-*-*.com/
---Share what you know. Learn what you don't.---

Hiya, Lookup the instr function..
--
Regards
Pieter Wijnen

http://www.thuleeng.com

Tough guys don't take backup - tough guys cry a lot..

Good site to look for Access answers is:
http://home.att.net/~dashish

You can use Left(), Right(), Mid(), and Instr() to parse strings.

There's a module in Microsoft's sample database neatcode.mdb with extra
functionality for handling strings.

The best method to read the file will depend on whether it is a fixed length
file format or not.

There are a couple of things to look up in help, start with open for input
or open for random, and then look up User-defined types.

You don't have to use a User-defined type of course, but it can make things
easier in some circumstances.  The type is declared outside the procedure
you are using it in, say in a module e.g.

Type MERecord
    Date As String * 10
    Time As String * 7
    LookingFor As String * 12
    Processed As String * 12
    From As String * 52
End Type

Here's some code I've used to read a file called packet.log and process it.
You'll need to use code to identify what type of information is on each line
in order to recognise what to do with it, and it may be getting too
complicated to use a User Defined Type, but it should give you an idea.

For my purposes, I read the file into an array, then manipulate the array.
BTW the 'me' stands for MaxExchange, don't get confused with the me keyword!

Sub Test()
Dim strPath As String
Dim intFF As Integer
Dim meRec() As MERecord
Dim mePosition As Long ' used to hold current line number
Dim i As Integer
Dim meLast As Integer    'holds number of lines
Dim meLine As String
Dim meFrom() As String
Dim strcomp As String

intFF = FreeFile
mePosition = 0
ReDim Preserve meRec(0)
Open strPath & "packet.log" For Input Access Read As #intFF

For i = 1 To 2    'discard the first two lines of the file
    Line Input #intFF, meLine
Next i

Do Until EOF(intFF)
    Line Input #intFF, meLine
    If mePosition > 0 Then
        ReDim Preserve meRec(UBound(meRec) + 1)
    End If

    meRec(mePosition).Date = Left(meLine, 12)
    meRec(mePosition).Time = Mid(meLine, 13, 7)
    meRec(mePosition).LookingFor = Mid(meLine, 21, 12)
    meRec(mePosition).Processed = Mid(meLine, 34, 12)
    meRec(mePosition).From = Right(meLine, 52)
    mePosition = mePosition + 1
Loop
meLast = mePosition - 1
Close #intFF

End sub

Obviously I then do a lot of other stuff with meRec() after this for my
purposes.  I hope this helps.

Phil
--
Phil Haddock

Applied Marketing Technologies
PO Box 754
Rozelle   NSW   2039
Australia