problem with parsing

[DeanRowe]

Good Morning,

When I normally parse a string or csv I use the split feature and the delimiter used (I normally use the ">" symbol as commas come up quite often and mess it up).

However I now have the problem of trying to parse data that someone sends me and I don't have any control over the format, and it doesn't use a delimiter!

It is set out in a standard format, so there are certain sentences in the string that I can use as a reference point, however I am unsure of how to do this.

I should mention i copy the string into a memo field on a form, I am then looking to press an event button and use vba to take the required data and enter it into my relevant fields.

If anyone could please help me to isolate the text from the string I would be very greatful, I have turned bold the text that can vary in both character length and number of lines used - and that I am trying to extract, everything that remains "unbold" is fixed and never changes....

Comany Name 
Thank you, payment for your order has been accepted. 
  
Item Name Quantity Price Total 
[B]HYT100-14 Inch Electric Push Lawnmower 1 £156.28 £156.28[/B] 

--------------------------------------------------------------------------------
 
  Subtotal £156.28 
Shipping is  Shipping £0.00 
  Tax (Inc) £23.28 
Payment method is 'Protx' 
--------------------------------------------------------------------------------
 
Currency is British Pound Total £[B]156.28[/B] 
  
Your details are shown below for order ref: [B]RC2789995[/B]
 
Title [B]Mr[/B] 
First Name [B]Joe[/B] 
Last Name [B]Bloggs[/B] 
Address 1 [B]3 Anywhere Road[/B] 
Address 2  [B]Anywhere Place[/B]
Town [B]LUTON[/B] 
County [B]Beds.[/B] 
Postcode [B]LU4 9TE[/B] 
Country UNITED KINGDOM 
Telephone [B]01234 567890[/B] 
Email [B]email@emailworld.com[/B] 
   
 

--------------------------------------------------------------------------------
 
Company Name
Company Address

--------------------------------------------------------------------------------

There is another format that they send, which also has a second address mentioned, however i hope if the above can be cracked then i can do the rest on my own.

I was thinking of approaching the code in the following manner:

me.customertitle.value = Split(StringField, "Title ")(0)

however this obviously doesnt work, and I am stumped! Any ideas would be a godsend!

Thank you for taking the time to read this

Dean

 

[DeanRowe]

Update:

I've looked into other expressions instead of split, such as left, right and mid but I can't seem to find a logical way to approach it. Does anyone know of any other expressions that I should look into as my searches on this forum and on google are coming up with these 4 expressions.

Thanks again

 

[gemma-the-husky]

if there is no rigid format for the string you will have little chance of doing this automatically, and will have to enter the details manually

to do this electronically surely you need to agree a standard structure for the message

 

[DeanRowe]

Hi Gemma,

Thank you for replying. it does have a certain structure/format, i.e. the data that i am after always falls between two unique strings. for example....

to retrieve the information "RC2789995"

it always comes after this string - "Your details are shown below for order ref: "
and it always comes before this string - "

Title"

These will always remain constant - if you ignore the part i wrote saying there is also another format, I can simply create a different event button to process data received in this format.

I can find similar "before" and "after" points for all the information I need to extract. Essentially what I need to know how to do is use a string, such as "Your details are shown below for order ref: " as a delimiter rather than a single character such as "," or ">".

Do you have any ideas? or know of how to make the example shown in this post to retrieve "RC2789995".

Thank you for your time Gemma.

btw - i love Huskies, second to wolves they are the most beautiful animals on the planet

 

[DJkarl]

Checkout the Instr function in conjunction with either left, right, or mid to analyze whether a string contains part of another string. However it may be faster to do this manually (unless you have hundreds or thousands of files). Analyzing and parsing this type of data is problematic at best, this kind of data is formatted for reporting/printing and not setup to be imported back into another system.

 

[DeanRowe]

at the moment we copy and paste the info into our form by hand, however we have hundreds to do a day and to do it manually will save us a fortune in time and money.

I've looked into the Instr function but no joy as yet, I'll keep scouring through google.

Thanks for your time DJKarl

 

[DCrake]

Simple Software Solutions

Have you tried the InStr() function

Essentially you are asking if a certain letter, phrase, digit, character exists in a known string and if so where in the string did you find it?

Example

iPos = Instr("this code finds: £1.30 at the 17 character in the string",":")

iPos = 17

So you could say that the value you want is Mid(MyString,17,5)

iValue = Mid(MyString,17,5)

iValue = £1.30

So if like you say you have constant phrases then look for the start and end phrases and extract whats in between.

Function ExtractedRefNo(Optional PreText As String, Optional PostText As String,StrSearchString As String) As String

Dim iStart As Integer
Dim iEnd As Integer

If Not IsEmpty(PreText) Then

iStart = Instr(StrSearchString,"order ref: ")

Endif

If Not IsEmpty(PostText) Then

iEnd = Instr(StrSearchString,"-")

Endif

If iEnd > iStart Then

iEnd = iEnd -1

ExtractedRefNo = Mid(StrSearchString,iStart,(iEnd-iStart))

Else

ExtractedRefNo = Mid(StrSearchString,iStart)

End If


In Summary

MyRef = ExtractedRefNo("order ref: ","-","Your order ref: RB12345 -")

would return

RB12345

Hope this makes sense

CodeMaster:

 

[DeanRowe]

thinking aloud for a moment....

Does anyone know if it would be possible to replace a string with a single character?

For example....

"Your details are shown below for order ref: " is replaced by a ">" and
"

Title" is replaced by a ">" - is also replaced by a ">"

If this is possible, I would then be left with the data i required seperated by ">" delimiters , i can then use the split function as normal.

If anyone knows if this is possible please let me know - I'll be searching the forum for replacing strings.

 

[Brianwarnock]

Replace is covered in ACCESS help.

Brian

 

[DeanRowe]

problem solved, will post later with solution after testing, thanks for your help and time everyone

dean

 

[RuralGuy]

Well it looks like you do not need this code I put together. It was fun anyway.

Public Function ParseTest()

Dim BigString As String
Dim strComp As String

BigString = "Company Name " & vbCrLf & "Thank you, payment for your order has been accepted." & vbCrLf & vbCrLf & _
"Item Name Quantity Price Total" & vbCrLf & "HYT100-14 Inch Electric Push Lawnmower 1 £156.28 £156.28" & vbCrLf & vbCrLf & _
"--------------------------------------------------------------------------------" & vbCrLf & vbCrLf & _
"  Subtotal £156.28" & vbCrLf & "Shipping is  Shipping £0.00" & vbCrLf & "  Tax (Inc) £23.28" & vbCrLf & _
"Payment method is 'Protx'" & vbCrLf & _
"--------------------------------------------------------------------------------" & vbCrLf & vbCrLf & _
"Currency is British Pound Total £156.28" & vbCrLf & vbCrLf & _
"Your details are shown below for order ref: RC2789995 " & vbCrLf & vbCrLf & _
"Title Mr" & vbCrLf & "First Name Joe" & vbCrLf & "Last Name Bloggs" & vbCrLf & _
"Address 1 3 Anywhere Road" & vbCrLf & "Address 2  Anywhere Place" & vbCrLf & "Town LUTON" & vbCrLf & _
"County Beds." & vbCrLf & _
"Postcode LU4 9TE" & vbCrLf & "Country UNITED KINGDOM" & vbCrLf & "Telephone 01234 567890" & vbCrLf & _
"Email email@emailworld.com" & vbCrLf & vbCrLf & vbCrLf & _
"-------------------------------------------------------------------------------" & vbCrLf & vbCrLf & _
"   " & vbCrLf & "Company Name" & vbCrLf & "Company Address" & vbCrLf & _
"--------------------------------------------------------------------------------"

MsgBox "[" & fParseLine(BigString, "Your details are shown below for order ref:", True) & "]"

End Function

Public Function fParseLine(InString As String, strCompare As String, _
                           Optional BeExact As Boolean = False) As String
'-- Return the string that starts after the strCompare string in InString
'-- ...up to the end of the current line.

'-- Lines are terminated with some sort of control code or sequence
'-- ...like vbCr or vbLf or vbCrLf

'-- That sequence is what we will use to determine the end of the line in InString

'-- We will not be case sensitive unless BeExact is True

   Dim i As Long
   Dim CompareType As Long
   If BeExact Then
      CompareType = vbBinaryCompare       '-- Do a binary compare (case sensitive)
   Else
      CompareType = vbTextCompare         '-- Do a texual compare (not case sensitive)
   End If
   i = InStr(1, InString, strCompare, CompareType)
   If i > 0 Then
      fParseLine = Trim(fRestOfLine(Mid(InString, i + Len(strCompare) + 1)))
   Else
      MsgBox "Unable to locate the string..." & vbCrLf _
             & "[" & strCompare & "]", vbCritical
   End If

End Function

Public Function fRestOfLine(InString) As String
'-- Return InString truncated just before any control codes in InString.

   Dim x As Integer
   For x = 1 To Len(InString)
      If Asc(Mid(InString, x, 1)) > 31 And Asc(Mid(InString, x, 1)) < 127 Then
         fRestOfLine = fRestOfLine & Mid(InString, x, 1)
      Else
         Exit For          '-- We've hit a control code or ASCII code > 127
      End If
   Next x

End Function

 

[DeanRowe]

it certainly looks impressive RuralGuy, and I'm starting to slow down now with my method I'm working on (got a post here thats stopped me in my tracks) so I'll be taking a look at this code, does it read the data from a field? I couldn't see a reference in there for the memo field where it is to take the data from?

thanks again RuralGuy

Dean

 

[RuralGuy]

Arguments are passed byVal as the default so just pass it the MemoField.
fParseLine(YourField, "Your details are shown below for order ref:", True)