String/Text Manipulations 
Author Message
 String/Text Manipulations

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.*-*-*.com/
---Share what you know. Learn what you don't.---



Sat, 03 Nov 2001 03:00:00 GMT  
 String/Text Manipulations

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

  wijnen.vcf
< 1K Download


Sat, 03 Nov 2001 03:00:00 GMT  
 String/Text Manipulations
Exporting clean data is always the preferred option. Access expects
one record per line, with fields either fixed-width or delimited.

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.

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?



Sun, 04 Nov 2001 03:00:00 GMT  
 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.---



Tue, 06 Nov 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Text String Manipulation

2. string manipulation, string routines, etc.

3. String manipulation

4. String Manipulation

5. VBA String Manipulation DLL Missing

6. String manipulation in VBA

7. String Manipulation

8. String manipulation with vbscript

9. String manipulation topic

10. String manipulation...

11. Newbie question : string manipulation with VBscript.

12. String manipulation

 

 
Powered by phpBB® Forum Software