Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #11279 > unrolled thread

Searching for text in a memo field between two speciific sentences

Back to article view | Back to comp.databases.ms-access

Contents

  Searching for text in a memo field between two speciific sentences David Mitchell <david.smile.photography@gmail.com> - 2015-10-30 08:02 -0700
    Re: Searching for text in a memo field between two speciific sentences Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-10-30 08:59 -0700
    Re: Searching for text in a memo field between two speciific sentences "Phil" <phil@stantonfamily.co.uk> - 2015-10-30 16:23 +0000
    Re: Searching for text in a memo field between two speciific sentences David Mitchell <david.smile.photography@gmail.com> - 2015-11-02 02:09 -0800
      Re: Searching for text in a memo field between two speciific sentences "Phil" <phil@stantonfamily.co.uk> - 2015-11-02 10:28 +0000
        Re: Searching for text in a memo field between two speciific sentences David Mitchell <david.smile.photography@gmail.com> - 2015-11-02 02:45 -0800
          Re: Searching for text in a memo field between two speciific sentences "Phil" <phil@stantonfamily.co.uk> - 2015-11-02 14:11 +0000
            Re: Searching for text in a memo field between two speciific sentences David Mitchell <david.smile.photography@gmail.com> - 2015-11-02 07:52 -0800
              Re: Searching for text in a memo field between two speciific sentences "Phil" <phil@stantonfamily.co.uk> - 2015-11-02 16:17 +0000
                Re: Searching for text in a memo field between two speciific sentences David Mitchell <david.smile.photography@gmail.com> - 2015-11-02 08:28 -0800
                  Re: Searching for text in a memo field between two speciific sentences "Phil" <phil@stantonfamily.co.uk> - 2015-11-02 16:42 +0000
                    Re: Searching for text in a memo field between two speciific sentences David Mitchell <david.smile.photography@gmail.com> - 2015-11-02 09:17 -0800
                      Re: Searching for text in a memo field between two speciific sentences "Phil" <phil@stantonfamily.co.uk> - 2015-11-02 22:54 +0000
                        Re: Searching for text in a memo field between two speciific sentences David Mitchell <david.smile.photography@gmail.com> - 2015-11-03 01:47 -0800
                          Re: Searching for text in a memo field between two speciific sentences David Mitchell <david.smile.photography@gmail.com> - 2015-11-03 03:04 -0800
                            Re: Searching for text in a memo field between two speciific sentences David Mitchell <david.smile.photography@gmail.com> - 2015-11-03 03:23 -0800
                              Re: Searching for text in a memo field between two speciific sentences David Mitchell <david.smile.photography@gmail.com> - 2015-11-03 05:03 -0800
                                Re: Searching for text in a memo field between two speciific sentences "Phil" <phil@stantonfamily.co.uk> - 2015-11-03 23:48 +0000
                                  Re: Searching for text in a memo field between two speciific sentences David Mitchell <david.smile.photography@gmail.com> - 2015-11-04 03:15 -0800
                                    Re: Searching for text in a memo field between two speciific sentences "Phil" <phil@stantonfamily.co.uk> - 2015-11-05 23:57 +0000
      Re: Searching for text in a memo field between two speciific sentences Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-11-02 06:17 -0800
        Re: Searching for text in a memo field between two speciific sentences David Mitchell <david.smile.photography@gmail.com> - 2015-11-02 07:55 -0800

Page 1 of 2  [1] 2  Next page →

#11279 — Searching for text in a memo field between two speciific sentences

We receive updates from orders via email and I am trying to select a part of the email to use in a field of a table.

The email has a line "Item Ordered:" followed by the product, size cost, etc - which is the info I need.
After the info I need is the Line "Order Status:"

Is there a way of just selecting the words after "Item Ordered:" and before "Order Status:"?

[toc] | [next] | [standalone]

#11280

On Friday, October 30, 2015 at 11:02:56 AM UTC-4, David Mitchell wrote:
> We receive updates from orders via email and I am trying to select a part of the email to use in a field of a table.
> 
> The email has a line "Item Ordered:" followed by the product, size cost, etc - which is the info I need.
> After the info I need is the Line "Order Status:"
> 
> Is there a way of just selecting the words after "Item Ordered:" and before "Order Status:"?

Here's a quick function.  You will need to modify to suit your needs as this is an example to help you get going.  Add this to a code module.  In your case, you might want to find the start of the string "Item Ordered:" and then add 12 to it (tem Ordered:) so you are at the end of it.

From the debug window I entered
  ? Extract("Pat")

Public Function Extract(strSearch As String) As String
    Dim s As String
    Dim iLen As Integer
    Dim iPos As Integer
    
    iLen = Len(strSearch)
    
    'ensure something was passed to this function
    If iLen > 0 Then
        s = "My name is Pat and I am a male"
        iPos = InStr(s, strSearch)
        
        If iPos > 0 Then
            Extract = Mid(s, iPos, iLen)
        End If
    End If

End Function

[toc] | [prev] | [next] | [standalone]

#11281

On 30/10/2015 15:02:52, David Mitchell wrote:
> We receive updates from orders via email and I am trying to select a part
> of the email to use in a field of a table.
> 
> The email has a line "Item Ordered:" followed by the product, size cost,
> etc - which is the info I need. After the info I need is the Line "Order
> Status:"
> 
> Is there a way of just selecting the words after "Item Ordered:" and
> before "Order Status:"?
> 

You are going to need the Instr function which finds one string within
another and the mid function which pulls bits of data out of a string

Try this

Function FindOrder(OrderStg As String, StartStg As String, EndStg As String,
Delimiter As String) '?FindOrder("This is the line with Item Ordered: Blue
Shirt, 16, £20.00 Order Status", "ItemOrdered", "Order Status", ",")

    Dim i As Integer, j As Integer
    Dim StartProduct As Integer
    Dim MyProduct As String
    Dim MySize As String
    Dim MyCost As String
    Dim Stg As String
    
    Stg = Trim(OrderStg)
    i = InStr(Stg, StartStg)
    StartProduct = i + Len(StartStg) + 1
j j = InStr(StartProduct, Stg, Delimiter) ' First Comma or space after the
item ordered MyProduct = Mid(Stg, StartProduct + 1, (j - StartProduct - 1))
    Debug.Print "Item: " & MyProduct
Stg Stg = Trim(Right(Stg, Len(Stg) - j)) ' Keep right hand part of order
    j = InStr(Stg, Delimiter)                   ' Comma after size
    MySize = Left(Stg, j - 1)
    Debug.Print "Size: " & Format(CDec(MySize), "Fixed")
Stg Stg = Trim(Right(Stg, Len(Stg) - j)) ' Keep right hand part of order
    j = InStr(Stg, EndStg)                      ' Comma after cost
    MyCost = Replace(Left(Stg, j - 1), "£", "")
    Debug.Print "Price: " & Format(CCur(MyCost), "Currency")
    
End Function

Phil

[toc] | [prev] | [next] | [standalone]

#11287

Thank you both very much for responding.  The level of coding is pushing my comfort zone!  Here are 3 examples:-

Items ordered:
Heidenau K75 4.00-8 55 M Front/Rear TT Quantity: 3 x 30 GBP 

Order status: Order accepted for the execution


Items ordered:
Matador MP54 Sibir Snow 165/65R15 81 T Quantity: 2 x 37 GBP 

Order status: Parcel has been shipped



Items ordered:
Gislaved EURO*Frost 5 SUV 255/55R18 109 H XL FR Quantity: 4 x 86 GBP 

Order status: In progress

[toc] | [prev] | [next] | [standalone]

#11288

On 02/11/2015 10:09:24, David Mitchell wrote:
> Thank you both very much for responding. The level of coding is pushing my
> comfort zone! Here are 3 examples:-
> 
> Items ordered:
> Heidenau K75 4.00-8 55 M Front/Rear TT Quantity: 3 x 30 GBP
> 
> Order status: Order accepted for the execution
> 
> 
> Items ordered:
> Matador MP54 Sibir Snow 165/65R15 81 T Quantity: 2 x 37 GBP
> 
> Order status: Parcel has been shipped
> 
> 
> 
> Items ordered:
> Gislaved EURO*Frost 5 SUV 255/55R18 109 H XL FR Quantity: 4 x 86 GBP
> 
> Order status: In progress
> 
> 
> 

Is the format of the line EXACTLY as your samples, including the Carriage
Return after the "Items Ordered:", and "Quantity:" space then a number space
X space and price, always followed by GBP? Are there 2 carriage returns
before the "Order Status:" and 1 space after.

It's my guess that if you are getting this information in an Email, the
format may vary.

Phil

[toc] | [prev] | [next] | [standalone]

#11289

Thanks again for your response. The email varies slightly in the text that comes before "Items ordered:" but there are always 2 x carriage returns before "Items Ordered", 1 x carriage return after "Items Ordered:" Then the line that contains the product details which always ends "Quantity:" then a space, then the number ordered, then a space then "x", then a space then a number (always a whole number), then a space, then "GBP" , then there are 3 carriage returns before "Order Status:", then a space, then one of the following:-

In Progress
Parcel has been shipped
Parcel was delivered

[toc] | [prev] | [next] | [standalone]

#11290

On 02/11/2015 10:45:41, David Mitchell wrote:
> Thanks again for your response. The email varies slightly in the text that
> comes before "Items ordered:" but there are always 2 x carriage returns
> bef ore "Items Ordered", 1 x carriage return after "Items Ordered:" Then
> the li ne that contains the product details which always ends "Quantity:"
> then a s pace, then the number ordered, then a space then "x", then a
> space then a n umber (always a whole number), then a space, then "GBP" ,
> then there are 3 carriage returns before "Order Status:", then a space,
> then one of the foll owing:-
> 
> In Progress
> Parcel has been shipped
> Parcel was delivered
> 

THis kept me ammused for a bit

Function ParseOrder(EMailLine As String, StartStg As String, QtyStg As
String, CurrencyStg As String, StatusStg As String) '?ParseOrder("This is the
line with Item Ordered: Heidenau K75 4.00-8 55 M Front/Rear TT Quantity: 3 x
30 GBP Order Status: Order accepted for the execution", "Item Ordered:",
"Quantity:", "GBP", "Order Status:")
'?ParseOrder("This is a different order with Item Ordered: Matador MP54 Sibir
Snow 165/65R15 81 T Quantity: 2 x 37 GBP Order Status:Parcel has been
shipped", "Item Ordered:", "Quantity:", "GBP", "Order Status:")
'?ParseOrder("Another order with Item Ordered:Gislaved EURO*Frost 5 SUV
255/55R18 109 H XL FR Quantity:4 x 86 GBP Quantity: 2 x 37 Euro Order
Status:In progress", "Item Ordered:", "Quantity:", "Euro", "Order Status:")
    
    Dim i As Integer
    Dim StartProduct As Integer
    Dim MyProduct As String
    Dim MySize As String
    Dim MyQty As String
    Dim MyPrice As String
    Dim OrderStatusStg As String
    Dim Stg As String
    
    Stg = Trim(EMailLine)
    i = InStr(Stg, StartStg)
    StartProduct = i + Len(StartStg) + 1
i i = InStr(StartProduct, Stg, QtyStg) ' Find "Quantity:"
    MyProduct = Mid(Stg, StartProduct, (i - StartProduct - 1))
    Debug.Print "Item: " & MyProduct
Stg Stg = Trim(Right(Stg, Len(Stg) - Len(QtyStg) - i + 1)) ' Keep right hand
part of order i = InStr(Stg, " X ") ' Look for the " X "
    MyQty = Left(Stg, i - 1)
    Debug.Print "Quantity: " & MyQty
Stg Stg = Trim(Right(Stg, Len(Stg) - i - 2)) ' Keep right hand part of order
without the " X " i = InStr(Stg, CurrencyStg) ' Find the currency
    MyPrice = Left(Stg, i - 1)
    Debug.Print "Price: " & MyPrice & CurrencyStg

    Stg = Trim(Right(Stg, Len(Stg) - Len(CurrencyStg) - Len(MyPrice)))
    i = InStr(Stg, StatusStg) + Len(StatusStg)
    OrderStatusStg = Mid(Stg, i)
    Debug.Print "Order Status: " & Trim(OrderStatusStg)
    
End Function

Phil

[toc] | [prev] | [next] | [standalone]

#11292

I'm struggling with this Phil :( 

I didn't think that there was a way to code this that took the information I wanted and split it into fields!  This is Utopia!!  On that basis the result of the Function should give "Product:" e.g. Gislaved EURO*Frost 5 SUV
255/55R18 109 H XL FR. "Quantity:" i.e. the number prior to the "x".  "Cost Price:" the price after the "x" and ignore the GBP as the value is always in British Pounds.  Finally, "OrderStatus:" e.g. parcel was delivered.

Again, many thanks for your help so far! 

[toc] | [prev] | [next] | [standalone]

#11294

On 02/11/2015 15:52:24, David Mitchell wrote:
> I'm struggling with this Phil :(
> 
> I didn't think that there was a way to code this that took the information
> I wanted and split it into fields! This is Utopia!! On that basis the
> result of the Function should give "Product:" e.g. Gislaved EURO*Frost 5
> SUV 255/55R18 109 H XL FR. "Quantity:" i.e. the number prior to the "x".
> "Cost Price:" the price after the "x" and ignore the GBP as the value is
> always in British Pounds. Finally, "OrderStatus:" e.g. parcel was
> delivered.
> 
> Again, many thanks for your help so far!
> 

I don't like the sound of "So far"

Phil

[toc] | [prev] | [next] | [standalone]

#11295

> 
> I don't like the sound of "So far"
> 
> Phil

Apologies for being presumptious.  Your help has been invaluable and it is my own lack of knowledge that exacerbates the problem. Please do not feel under any obligation to help further  :)

[toc] | [prev] | [next] | [standalone]

#11296

On 02/11/2015 16:28:33, David Mitchell wrote:
> 
>>
>> I don't like the sound of "So far"
>>
>> Phil
> 
> Apologies for being presumptious. Your help has been invaluable and it is
> my own lack of knowledge that exacerbates the problem. Please do not feel
> under any obligation to help further :)
> 

If you need more help, just ask. My last comment shouldn't have been taken
seriously.

I dare say you will need to put the results into a table. The Debug lines
should do that for you, but if the Order Status can have only a few values,
put those into a separate table OrderStatus table, and only hold the key in
the Orders Table. Saves on space

Phil

[toc] | [prev] | [next] | [standalone]

#11297

Thank you Phil.  My learning curve with Access has been a steep one!  Part of my confusion comes from the variables being passed to the function.  I thought it should be just the field "body" from the table linked to a specific folder in MS Outlook where all of the emails are routed via a rule set up to move them there.  Is there a way to "hardwire" the function with this single variable (EMailLine)??

EMailLine As String - comes from field Body
StartStg As String - is always "Item Ordered:"
QtyStg As String - is contained within the text from field "Body"
CurrencyStg As String - is always "GBP"
StatusStg As String - is contained within the text from field "Body" after "Order Status: "

[toc] | [prev] | [next] | [standalone]

#11299

On 02/11/2015 17:17:48, David Mitchell wrote:
> Thank you Phil. My learning curve with Access has been a steep one! Part
> of my confusion comes from the variables being passed to the function. I t
> hought it should be just the field "body" from the table linked to a
> specif ic folder in MS Outlook where all of the emails are routed via a
> rule set u p to move them there. Is there a way to "hardwire" the function
> with this single variable (EMailLine)??
> 
> EMailLine As String - comes from field Body
> StartStg As String - is always "Item Ordered:"
> QtyStg As String - is contained within the text from field "Body"
> CurrencyStg As String - is always "GBP"
> StatusStg As String - is contained within the text from field "Body" after
> "Order Status: "
> 

No problem

Basically instead of telling the function what to look for you pre-define the
"fixed information" as a constant. The problem with this approach is that
heaven forfend that we ever go into the Euro, a user can't use this function
for euro currency. Equally, if you want to change the wording, yes it's a
piece of cake for the programmer to do it, but not the user

Here is the new code

Function ParseOrder2(EMailLine As String)
'?ParseOrder2("This is the line with Item Ordered: Heidenau K75 4.00-8 55 M
Front/Rear TT Quantity: 3 x 30 GBP Order Status: Order accepted for the
execution") '?ParseOrder2("This is a different order with Item Ordered:
Matador MP54 Sibir Snow 165/65R15 81 T Quantity: 2 x 37 GBP Order
Status:Parcel has been shipped") '?ParseOrder2("Another order with Item
Ordered:Gislaved EURO*Frost 5 SUV 255/55R18 109 H XL FR Quantity:4 x 86.05
GBP Quantity: 2 x 37 Euro Order Status:In progress")

    Dim i As Integer
    Dim StartProduct As Integer
    Dim MyProduct As String
    Dim MySize As String
    Dim MyQty As String
    Dim MyPrice As String
    Dim OrderStatusStg As String
    Dim Stg As String

    Const StartStg As String = "Item Ordered:"
    Const QtyStg As String = "Quantity:"
    Const CurrencyStg As String = "GBP"
    Const StatusStg As String = "Order Status:"
    
    Stg = Trim(EMailLine)
    i = InStr(Stg, StartStg)
    StartProduct = i + Len(StartStg) + 1
i i = InStr(StartProduct, Stg, QtyStg) ' Find "Quantity:"
    MyProduct = Mid(Stg, StartProduct, (i - StartProduct - 1))
    Debug.Print "Item: " & MyProduct
Stg Stg = Trim(Right(Stg, Len(Stg) - Len(QtyStg) - i + 1)) ' Keep right hand
part of order i = InStr(Stg, " X ") ' Look for the " X "
    MyQty = Left(Stg, i - 1)
    Debug.Print "Quantity: " & MyQty
Stg Stg = Trim(Right(Stg, Len(Stg) - i - 2)) ' Keep right hand part of order
without the " X " i = InStr(Stg, CurrencyStg) ' Find the currency
    MyPrice = Left(Stg, i - 1)
    Debug.Print "Price: " & MyPrice & CurrencyStg

    Stg = Trim(Right(Stg, Len(Stg) - Len(CurrencyStg) - Len(MyPrice)))
    i = InStr(Stg, StatusStg) + Len(StatusStg)
    OrderStatusStg = Mid(Stg, i)
    Debug.Print "Order Status: " & Trim(OrderStatusStg)
    
End Function

[toc] | [prev] | [next] | [standalone]

#11303

Works perfectly Phil.  My sincerest gratitude!  I will need to go through the code line by line and learn exactly how it does what it does.  Thanks again.

David

[toc] | [prev] | [next] | [standalone]

#11304

Spoke too soon (and left myself very embarrassed!)   I only tested it with the critical bit of the body of the email and not with all of it. There is a multitude of text both before and after the critical bit of the email which means your code selects Product as everything upto and including the Product and selects OrderStatus as everything from OrderStatus onwards.  Here is an example of the full email body, it does vary but always has the critical bit in the same format.  My apologies if I didn't make this clear earlier.


The number of your parcel is:05258058177538


To follow the path of your package please click on the following link:

http://extranet.dpd.de/cgi-bin/delistrack?pknr=05258058177538&typ=1&lang=en <http://extranet.dpd.de/cgi-bin/delistrack?pknr=05258058177538&typ=1<=en> 



Dear customer your parcel has been delivered.


Order number: 23167714


Date of order: 2015-10-24
Date of change in the order's status: 2015-10-29


Contact details for delivery:


Receiver (e.g. name of company): Godsman Tyres
Receiver (e.g. first or last name): David Mitchell
Address: na Blarlaith Ind Estate
Postcode: IV19 1EB
City: Tain


Items ordered:
Cooper Discoverer M+S Sport 235/65R17 108 H xL Quantity: 1 x 85 GBP 



Order status: Parcel was delivered

In urgent cases relating to the order, you can contact us by e-mail, responding to the automatically generated confirmation message.
E-mail: blahblah

Yours sincerely, Team OPONEO.CO.UK.
OPONEO.CO.UK 2015
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7163 / Virus Database: 4457/10913 - Release Date: 10/29/15

[toc] | [prev] | [next] | [standalone]

#11305

My head hurts but I'm going to work this out myself!  I just have two questions, does the fact that there are carriage returns in the body of the email being passed to the function make any difference. And, once I've worked through exactly what the function is doing, can I post up the code with my comments just for confirmation I have got it right?

[toc] | [prev] | [next] | [standalone]

#11307

My error.  The text is Items not Item which meant the instr function couldn't find it so started at the beginning of the email.  Working on trimming the last part now :)  Learning a lot about Mid, Trim and Instring

[toc] | [prev] | [next] | [standalone]

#11313

On 03/11/2015 13:03:12, David Mitchell wrote:
> My error. The text is Items not Item which meant the instr function
> couldn't find it so started at the beginning of the email. Working on
> trimming the last part now :) Learning a lot about Mid, Trim and Instring
> 

The bit of code I sent you was very Micky Mouse.
I use a vaguely similar routine to extract share prices from web sites.
As each web site is different, I have to tell the program what to look for in
each site, to get the prices I want.

Exactly the same method should be possible to extract all the data you want,
and, providing you control the nature of the email, you can extract any
information you want. The fields that you reqire are held in a table so they
can be changed without changing code. So Parcel no, Order No Chaned date etc.
etc can all be read and loaded into an Access table.

I am working on it for my own amusement, but it will take a couple of days.
If you like, I will keep you posted, but I warn you there is some pretty
stiff logic required.

I store my website body in a temporary file that I can view using Notebook.
Where do you store your Email body? 

Phil

[toc] | [prev] | [next] | [standalone]

#11314

Wow!  Do you mean you save the webpage in text format and then manipulate it via access?  Just when I thought I'd reached my end game in Access!  There are a multitude of possibilities I can think of!  Take as long as you like Phil. And yes, please keep me posted - stiff logic is just what I need to keep the grey matter ticking over.

I have linked the folder in Outlook where all of the emails reside, with a table in access.  As you can see from my initial post, I only wanted to have the line containing all of the details which would have been sufficient for my original thought.  Your help has been invaluable in opening my eyes to much greater possibilities.

Thank you again!

[toc] | [prev] | [next] | [standalone]

#11317

On 04/11/2015 11:15:38, David Mitchell wrote:
> Wow! Do you mean you save the webpage in text format and then manipulate i
> t via access? Just when I thought I'd reached my end game in Access! Ther
> e are a multitude of possibilities I can think of! Take as long as you lik
> e Phil. And yes, please keep me posted - stiff logic is just what I need
> to keep the grey matter ticking over.
> 
> I have linked the folder in Outlook where all of the emails reside, with a
> table in access. As you can see from my initial post, I only wanted to hav
> e the line containing all of the details which would have been sufficient
> f or my original thought. Your help has been invaluable in opening my eyes
> t o much greater possibilities.
> 
> Thank you again!
> 
> 

It's hairy stuff, but kept me out of mischief for a day.
I think it would be too difficult to get you started using the newsgroup.

There's a mass of code and tables to design and fill very accurately.
However, it pulls whatever information a require from either page source of a
website or if I pasted your full email into Notebook, I could extract any
data you needed If you want to persue it further, email me, and we will have
to do it over Skype

Phil

[toc] | [prev] | [next] | [standalone]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | comp.databases.ms-access

csiph-web