
String/Text Manipulations
There are two phases to this problem. The first is to read the file, the
second is to process each line of the file.
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
Quote:
> Can anyone give me any information on Access's ability (or inability, as
> I seem to be finding out) to process strings? I'm doing a data
> conversion from a flat file that's in a funky format.
> 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.deja.com/ ==--
> ---Share what you know. Learn what you don't.---