string parising - problem with the commas

[Guest_imported]

Hello,
I get a good string parsing when the delimiter is a comma. This works fine when the comma is properly placed. But! some commas in the data field are stuck right next to the following letters, like this.
Morgan Jim ,66 Hefty st, Los Angeles, Ca.,lawyer
It will not pick up the comma before 66 Hefty st since it is stuck right up against the letter. Can anyone give me ideas how to sort this thing out? This code here works fine!! while the comma is properly placed.

SELECT data2&& data is a field called "data"
GO TOP
SCAN
IF EMPTY(DEX2.A1)

replace all Data2.a1 with left(data,at(",",data)-1)
replace all Data2.a2 with substr(data,at(",",data)+1,at; (',',data,2)-at(",",data)-1)
replace all Data2.a3 with substr(data,at(",",data,2)+1,at;(',',data,3)-at(",",data,2)-1)
replace all Data2.a4 with substr(data,at(",",data,3)+1,at; (',',data,4)-at(",",data,3)-1)
replace all Data2.a5 with substr(data,at(",",data,4)+1,at;(',',data,5)-at(",",data,4)-1)

jonezbuz@aol.com

 

[baltman]

A good start, if you know how many fields you're supposed to get, is to use OCCURS(",",StringData) to identify if you have one or more extra delimiters.

If you know that the problem will likely only occur in the address field, you can remove the Nth comma for strings with one too many...

Brian

 

[rgbean]

Another (easier?) way to parse these strings is to use the functions in FoxTools - WORDS() and WORDNUM() [ or in VFP 7.0 use GETWORDCOUNT() and GETWORDNUM() ].

* make sure we've got spaces between all the commas
lcData = strtran(lcData,",,", ", ,") 
* do it twice just in case you have three ,'s in a row
lcData = strtran(lcData,",,", ", ,") 
* without the space, the word count is wrong
lnfields = words(lcData, ",")
DIMENSION laFields[lnFields]
FOR lnii = 1 to lnFields
   laFields[lnii] = wordnum(lcData, lnii, ",") && note: everything stored as a character field
ENDFOR

Now you can just pick the values out of the array, and as Brian suggests, you can verify you have the right number of fields.

Rick

 

[fruityBoy]

Hi, this is the method I use for parsing delimited strings

FOR lnindex = 1 TO LEN(lcpercs)
lcchar = SUBSTR(lcpercs,lnindex,1)
IF lcchar == LCDELIM
** this is our value
DIMENSION lapercs[lncount]
lapercs[lncount] = VAL(lcvalue)
lcvalue = ''
lncount = lncount +1
ELSE
lcvalue= lcvalue + lcchar
endif
NEXT lnindex && <--- next lnindex
** catch the end of string where there is no delimiter
DIMENSION lapercs[lncount]
lapercs[lncount] = VAL(lcvalue)

basically, this reads one character at a time, into a memory variable, when a delimiter is hit the variable is stored into an array, and the variable is reset, in order to build up the next entry.

I've put a bit of code at the end to catch the last entry, because obviously there is no delimiter.

Hope this helps.

Regards

Simon