Capturing data from a memo field 3

[PBREP]

Hi All:

I have a memo field (i.e. TrackNo, data come in and I would like to capture the data between the carots (^)and copy it to another field (i.e. AnyText).

I.e. 1234^56789^9876 = Need to grab "56789"

Anyone know the best command in VFP 7.0 to do this?

Thank you,
~PM

 

[craigsboyd]

Look up

GETWORDNUM(cString, nIndex[, cDelimiter])

or

ALINES(ArrayName, cExpression [, lTrim] [cParseChar, ,… ,cParseChar])

in the VFP help file. Both allow for a delimiter or Parse character such as the "^" you speak of.

_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[ChrisRChamberlain]

PBREP

Another alternative

[COLOR=blue]lcstring = [1234^56789^9876]
lcstring = SUBSTR(lcstring,AT([^],lcString,1)+ 1,AT([^],lcstring,2) - 1 - AT([^],lcstring,1))

? lcString[/color]

FAQ184-2483 - answering getting answered.​

Chris
PDFcommander^tm.com
PDFcommander^tm.co.uk

 

[PBREP]

Thanks Guys!

One more question. data comes across as: $Electra$Carmine$L$
I need to remove all the dollar signs, add one common to the second dollar sign and add a space for the third dollar sign.

Look like this: "Electra, Carmine L"

Thanks-a-million!

~PM

 

[rgbean]

If the comma is always to be placed at the second $, then this code would work:

lcstring = [$Electra$Carmine$L$]
lcstring = LTRIM(CHRTRAN(STUFF(lcstring,AT("$",lcstring,2),1,","),"$"," "))

Rick

 

[ChrisRChamberlain]

PBREP

Alternatively if the code is inconsistent, look at the following code and you can see how you can 'walk through' the string using the AT() function to determine the lengths of individual substrings.

[COLOR=blue]lcstring = [$Electra$Carmine$L$]
lcstring1 = SUBSTR(lcstring,AT([$],lcString,1) + 1,AT([$],lcstring,2) - 1 - AT([$],lcstring,1)) + [,]
? lcstring1[/color]

You can then concatenate all three substrings, lcstring1, (shown), lcstring2, and lcstring3, (not shown), to give you the total string required.

FAQ184-2483 - answering getting answered.​

Chris
PDFcommander^tm.com
PDFcommander^tm.co.uk

 

[PBREP]

Thanks Rick.

Good point Chris!!!

~PM

 

[jimoo]

You may find this FAQ handy too.

faq184-4326 Parsing Delimited Text

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[keepingbusy]

Searching through the forum and this thread almost answers my question. I am trying to extract parts of a memo field but unlike PBREP's problem I need to extract info between words, for example:

Order From: Dave Smith, An address somewhere, Tel No 01234567890 End

I dont need the Order From: and End words left in the string

Chris posted

lcstring = [1234^56789^9876]
lcstring = SUBSTR(lcstring,AT([^],lcString,1)+ 1,AT([^],lcstring,2) - 1 - AT([^],lcstring,1))
? lcString

I have tried changing the ^ character but without success, can anyone please suggest what I'm missing here?

Many thanks
Lee

VisFox Version 6 User / Windows ME

 

[rgbean]

Lee,
This works (although there are many other possibilities!):

lcString = "Order From: Dave Smith, An address somewhere, Tel No 01234567890 End"
lcResult = LTRIM(STRTRAN(lcString,"Order From:",""))
lcResult = RTRIM(LEFT(lcResult, RAT("End",lcResult)-1))
?lcResult

Rick

 

[keepingbusy]

Hi Rick

Perfect, although I did at first receive an error message and noticed that the lcstring variable should have been lcresult. No problem, I changed these to:

lcResult = "Order From: Dave Smith, An address somewhere, Tel No 01234567890 End"
lcResult = LTRIM(STRTRAN(lcResult,"Order From:",""))
lcResult = RTRIM(LEFT(lcResult, RAT("End",lcResult)-1))
?lcResult

and needless to say it works fine

Thank you for your reponse and for resolving my problem for which I'm grateful

Lee

VisFox Version 6 User / Windows ME

 

[keepingbusy]

Forgot to mention, thanks to PBREP for allowing me to tag on to the end of this thread! Hope it was useful to other Forum users

Lee....

VisFox Version 6 User / Windows ME

 

[keepingbusy]

Just a minor issue...

The set up at the moment allows a user to paste the body of an email into a memo field. I then use:

STORE EMAILBODY TO lcResult
lcResult = LTRIM(STRTRAN(lcResult,"PACKING SLIP:",""))
lcResult = RTRIM(LEFT(lcResult, RAT("Purchased on:",lcResult)-1))
?lcResult

The problem is there are two "PACKING SLIP"'s one in lower case the other in upper case.

How can I get the above code to only recognise the upper case PACKING SLIP?

I've tried UPPER() etc and storing the PACKING SLIP to a variable but to no avail.

Any suggestions would be appreciated
Many thanks
Lee....

VisFox Version 6 User / Windows ME

 

[rgbean]

Lee,
The good news is that starting in VFP 7.0 (and later), there is an optional Flag that allows case insenitive recognition. The bad news is that in 6.0 (and earlier) you'll need to check for both variants. e.g.

STORE EMAILBODY TO lcResult
lcResult = STRTRAN(lcResult,"packing slip:","")
lcResult = LTRIM(STRTRAN(lcResult,"PACKING SLIP:",""))
lcResult = RTRIM(LEFT(lcResult, RAT("Purchased on:",lcResult)-1))
?lcResult

Rick

 

[keepingbusy]

Hi Rick

Thank you for your response but unfortunately its still not working as it is finding the first "Packing Slip" and not the "PACKING SLIP". I have copied your code and tried it so am I missing something?

Lee.....

VisFox Version 6 User / Windows ME

 

[rgbean]

Lee,
I'm back to confused. While "PACKING SLIP" is definitly UPPERCASE, "Packing Slip" isn't lower case. I believe this would best be described PROPER case - it's what PROPER("packing slip") would return.

So while I can understand that it wouldn't find "Packing Slip:", I don't understand why my code wouldn't find "PACKING SLIP:".

Can you show us what is in EMAILBODY for this situation and what you want returned?

Rick

 

[keepingbusy]

Rick

Sorry for the confusion, hope the below helps:

Your buyer's delivery address is below (please use your own address as the return address and enclose the packing slip for your buyer's reference):

Here are the details of your completed company name sale:

Order #: 123-4567890-1234567
Listings: 3
Total Item Count: 3
Listing 1: The Best of the hits [Audio CD] Some Artist

Postage & Handling: ?3.72
Total Amount: (?37.57)
----------
For general questions about abcdef.co.uk Marketplace, you
will find more information here:

http://www.abcdef.co.uk

PACKING SLIP:
abcedf.co.uk Marketplace Item: The Best of the hits [Audio CD] Some Artist

The best of
Listing ID: 12345678901
SKU: 09876543212
Quantity: 1
abcdef.co.uk Marketplace Item: The Best of the hits [Audio CD] Some Artist

Purchased on: 01.01.2010
Dispatched by: abcdef.co.uk

Many thanks
Lee

VisFox Version 6 User / Windows ME