parse a block of text

[juggleboy]

Hello

-------- 07/09/11 15:39:07 LINE = 0124 STN = 880
BC = SPEECH
PRI SERVICE UNKNOWN
00:00:00 OUTGOING CALL
DIGITS DIALED 651178
00:01:11 CALL RELEASED

I have a few blocks of text like the above in one field. There are two types of text block one for outgoing calls and one for incoming calls. What I would like to know is would it be possible to:

1- separate one block from another where they are all grouped together, to distinguish what is the beginning of a particular text block would be the ---- and then the "call release" would designate the end of a block, there is also a blank line in-between each block as well.

2 - If I can separate each block out, could I then group each block of text by the line number and whether it was outgoing or incoming?

Thanks If anyone here could guide me in the right direction I would be really grateful.

[dicepackage]

If you want to read in the whole thing, assuming one record starts at -------- and ends at CALL RELEASED you could try this. In this example you are reading from the string Datafeed from the position of -------- to CALL RELEASED. On every read of the record the datafeed string will get smaller as it keeps progressing through. Continue this code for your records or work in a loop statement. I have not tested this code.


Dim Datafeed as String;
' Place your code to read your data into Datafeed here
Datafeed = Mid(Datafeed,InStr(Datafeed, "--------")+8,Len(Datafeed)
record1 = Left(Datafeed, InStr(Datafeed, "CALL RELEASED"))
Datafeed = Mid(Datafeed,InStr(Datafeed, "--------")+8,Len(Datafeed)
record2 = Left(Datafeed, InStr(Datafeed, "CALL RELEASED"))
'Continue with the two lines above repeating

[hansup]

If your Access version is 2000 or newer, you can use the Split() function to split your text field on the blank lines.

Public Sub SplitOnBlankLines(pInput)
Dim varElements As Variant
Dim i As Long
varElements = Split(pInput, vbCrLf & vbCrLf)
For i = 0 To UBound(varElements)
Debug.Print "Block " & i & ": " & varElements(i)
Next i
End Sub
Testing that function in the Immediate Window gives me this:

Call SplitOnBlankLines("one" & vbCrLf & vbCrLf & _
"two" & vbCrLf & vbCrLf & "three")
Block 0: one
Block 1: two
Block 2: three

You will want to do something with the text blocks other than Debug.Print, but I don't understand what it is you want to do. You lost me on the second part of your question.

[juggleboy]

@ hansup

Sorry I shall clarify that for you.

I have one memo field that stores about 10 A4 pages worth of these text blocks, in the current format they are not useful. What I need to do is separate each block of text as above into an independent record eg for line 0124 you would store: the date of each call, its duration, and digits dialled etc.

Thanks for the suggestions

[hansup]

What I need to do is separate each block of text as above into an independent record eg for line 0124 you would store: the date of each call, its duration, and digits dialled etc. Earlier you said "group each block of text by the line number and whether it was outgoing or incoming". So apparently "group by" meant something like "extract and store". :think:

I gave you a method to extract the call information text blocks from the memo field. That part was quick and easy. The next steps will require much more effort and I don't have code to offer you for those steps.

You can use InStr() and Mid() functions similarly to the approach dicepackage suggested. Or, if you know how to use regular expressions from VBA, that could be a reasonable approach.