Parsing Problem 2

[keepingbusy]

Hi all

Can anyone suggest a sollution for the following?

In a table there are list of artists in a field called ARTISTTEMP and in a format such as DIAMOND,NEIL or ROSS,DIANA

I use the following to reverse the names and remove the comma with the result NEIL DIAMOND or DIANA ROSS

SCAN
  STORE SPACE(100)  TO mfull
  STORE ARTISTTEMP  TO mfull
  STORE SPACE(50)   TO nSplit, msur, mfor
  nSplit = AT(",",mfull)
  msur = LEFT(mfull, nSplit - 1)
  mfor = SUBSTR(mfull, nSplit + 1)
  REPLACE ARTISTTEMP WITH ALLTRIM(mfor)+" "+ALLTRIM(msur)
ENDSCAN

When a record contains more than one artist such as BENSON,GEORGE / JARREAU,AL I am getting results that look like GEORGE / JARREAU AL BENSON when it shoud read GEORGE BENSON / AL JARREAU

Is there a way to overcome this?

I am using Version 9

Thank you in anticipation.

Lee


Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk

 

[JanBucek]

I thing you have to use ALines() in steps:
step 1- parse the names delimited by / into array laNames:
step 2- parse each item of laNames delimited by , into single names, reverse and concatenate it together:

local array laNames(1), laX(1)
local mFull, nCount, lcResult, cItem

mFull="BENSON,GEORGE / JARREAU, AL"
nCount=ALines(laNames, chrtran(mFull, "/", chr(10)), .T.)
lcResult=""
FOR each cItem in laNames
nCount=ALines(laX, chrtran(cItem, ",", chr(10)), .T.)
lcResult=lcResult+laX(2)+" "+laX(1)+" / "
ENDFOR
lcResult=left(lcResult, len(lcResult)-3)
? lcResult

 

[keepingbusy]

Hi JanBucek

Thank you for the prompt reply.

I changed the line mFull="BENSON,GEORGE / JARREAU, AL" to the field name mFull=ARTISTTEMP and the ran the code in a SCAN ... ENDSCAN

An error came up saying "Subscript is outside defined range"

lcResult=lcResult+laX(2)+" "+laX(1)+" / "

When I run the code with the mFull="BENSON,GEORGE / JARREAU, AL" it works so I'm guessing it's something to do with the field ARTISTTEMP.

Your thoughts would be appreciated

Lee


Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk

 

[JanBucek]

Yes, of course. The code I have designed was only a sample. You have to adapt it for cases of only one or more than two parts in one name and so on.

 

[mmerlinn]

[ ]

I would do it something like this:


SCAN
mFull = ARTISTTEMP
cLeft = ATXLEFT(mFull, '/')
cRight = ATXRIGHT(mFull, '/')
mFull = ATXLEFT(cLeft, ',') + ' ' + ATXRIGHT(cLeft, ',')
IF NOT EMPTY(cRight)
mFull = mFull + '/' + ATXLEFT(cRight, ',') + ' ' + ATXRIGHT(cRight, ',')
ENDIF
REPLACE ARTISTTEMP WITH mFull
ENDSCAN


This works regardless whether you have LASTNAME, FIRSTNAME or you have LASTNAME, FIRSTNAME/LASTNAME, FIRSTNAME mixed in the same field.

I did not test this, so test it thoroughly before you use it.

See faq184-5975 for code for the ATXLEFT() & ATXRIGHT() UDFs.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[mmerlinn]

[ ]

GRRRRR. I mixed up ATXLEFT() & ATXRIGHT() in my last post.

These lines:


mFull = ATXLEFT(cLeft, ',') + ' ' + ATXRIGHT(cLeft, ',')
mFull = mFull + '/' + ATXLEFT(cRight, ',') + ' ' + ATXRIGHT(cRight, ',')


Should be:


mFull = ATXRIGHT(cLeft, ',') + ' ' + ATXLEFT(cLeft, ',')
mFull = mFull + '/' + ATXRIGHT(cRight, ',') + ' ' + ATXLEFT(cRight, ',')


********************

If it is possible that you will have more than two name pairs, then something like this would work:


SCAN
mFull = ARTISTTEMP
x = 0 && Counter

DO WHILE NOT EMPTY(mFull)
x = x + 1
DIMENSION aWord(x)
aWord(x) = ATXLEFT(mFull, '/')
mFull = ATXRIGHT(mFull, '/')
ENDDO

DO WHILE NOT EMPTY(x)
mFull = ATXRIGHT(aWord(x), ',') + ' ' + ATXLEFT(aWord(x), ',') + IIF(EMPTY(mFull), '', '/') + mFull
x = x - 1
ENDDO

REPLACE ARTISTTEMP WITH mFull
ENDSCAN


This works no matter how many name pairs you have. If ARTISTTEMP is empty no changes are made.

Again, I did not test this, so test it thoroughly before you use it.

See faq184-5975 for code for the ATXLEFT() & ATXRIGHT() UDFs.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Imaginecorp]

Put this into your scan

T = "Benson,Al / Joel,Billy"
If At("/",T) > 0
	name1 = Strextract(T,"","/")
	name2 = Strextract(T,"/","")
	? Strextract(name1,",") +","+Strextract(name1,"",",")
	? Strextract(name2,",") +","+Strextract(name2,"",",")
Else
	? Strextract(T,",") +","+Strextract(T,"",",")
Endif

Again: You will have to fine tune

mmerlinn: what is ATXRIGHT, is this VFP?

 

[Imaginecorp]

Put this into your scan

I meant "Try This

 

[keepingbusy]

mmerlinn

Thank you for the additional code but there was an error with ATXRIGHT. Imaginecorp has pointed this out. Is it a typo?

Imaginecopr

I tried changing the code you supplied but I'm not sure of the concept:

[b]SCAN[/b]
T = "Benson,Al / Joel,Billy"
If At("/",T) > 0
    name1 = Strextract(T,"","/")
    name2 = Strextract(T,"/","")
    ? Strextract(name1,",") +","+Strextract(name1,"",",")
    ? Strextract(name2,",") +","+Strextract(name2,"",",")
Else
    ? Strextract(T,",") +","+Strextract(T,"",",")
Endif
[b]ENDSCAN[/b]

If I change T = ARTISTTEMP (The field name) how do I replace it with the changed information after Strextract etc.

I did try:

REPLACE ARTISTTEMP WITH ;
  Strextract(name1,",") +","+ ;
  Strextract(name1,"",",")+" "+ ;
  Strextract(name2,",") +","+ ;
  Strextract(name2,"",",")

but this produces some strange effects in that commas are left in the field and some fields are blank.

What I am doing wrong here?

Many thanks
Lee


Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk

 

[Imaginecorp]

*STREXTRACT(cSearchExpression, cBeginDelim [, cEndDelim [, nOccurrence[, nFlag]]]])

Scan
	cName = Alltrim(<<tablename>>.ARTISTTEMP)
	If At("/",cName) > 0
		***check if there is a back slash, if the seprator is something
		*** else use that here i.e. Period etc
		name1 = Strextract(cName,"","/")
		name2 = Strextract(cName,"/","")
		*** check for a comma, if seperator for last and first name
		*** is something else use that here i.e. Space. If space
		*** increase the width of the Above quotes with nothing in them
		*** same for the next 2 code
		cName1 = Strextract(name1,",") +","+Strextract(name1,"",",")
		cName2 = Strextract(name2,",") +","+Strextract(name2,"",",")
		Replace ARTISTTEMP With cName1
		*** you better do a replace then an insert as now you have 2 records
         	*** due to the fact there are 2 names
                *** INSERT cName2
	Else
		**** single name
		cName1 = Strextract(cName,",") +","+Strextract(cName,"",",")
		Replace ARTISTTEMP With cName1
		** no insert as its one name
	Endif
Endscan

Again: this is to give you an idea, you will have to fine tune

 

[keepingbusy]

Hi Imaginecorp

Thank you for the response. I'll take a look at fine tuning what you have kindly posted. When I run the code if there is only one name it removes it completely from the record and just leaves a comma and when there are two names it removes one of them (the second one).

I think you have put me on the right track so I'll post back when I've managed to work out what is happening.

Thanks again and I'm hoping mmerlinn will post back and tell us what ATXLEFT and ATXRIGHT are.

Lee


Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk

 

[Imaginecorp]

Lee:

So Sorry. I see now what you want to do. Put both names with a seperator "/" in the same field but in First Name , Last name order...

Give me a minute and I will write the code for you

 

[keepingbusy]

Imaginecorp

Perhaps I should have explained it a bit clearer (sorry). What you have described is exactly what I'm trying to do.

Lee

Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk

 

[Imaginecorp]

Scan
	cName = Alltrim(<<tablename>>.ARTISTTEMP)
	If At("/",cName) > 0
		name1 = Strextract(cName,"","/")
		name2 = Strextract(cName,"/","")
		cName1 = Strextract(name1,",") +" "+Strextract(name1,"",",")
		cName2 = Strextract(name2,",") +" "+Strextract(name2,"",",")
		Replace ARTISTTEMP With cName1 +"/"+cName2
	Else
		**** single name
		cName1 = Strextract(cName,",") +" "+Strextract(cName,"",",")
		Replace ARTISTTEMP With cName1
	Endif
Endscan

You are Using a "/" as a seperator for 2 names right?

Again: this is to give you an idea, you will have to fine tune

 

[keepingbusy]

Hi Imaginecorp

After some fine tuning I've managed to get it working thanks to your help.

I have added a couple of additional lines to the code as in some cases when certain titles were encountered it removed them completely. Whilst there may be an easier way, I don't see any problem with using the below as it runs quickly, sorting through over 200,000 titles in a short time.

One of the problems encountered was when it came across titles such as:

SUPERMAN RETURNS / (WS DUB SUB AC3 DOL)
PIRATES OF CARIBBEAN: DEAD MAN'S CHEST / (WS DUB)

These were removed and titles not containing a comma were also removed.

Anway, as mentioned, it works perfectly so a star for you and my thanks for your time and advice on this thread.

SCAN
  cName = Alltrim(ARTISTTEMP)
  IF NOT "," $ ARTISTTEMP
    LOOP
  ENDIF
  If At("/",cName) > 0
      IF ":" $ cName
*      DO NOTHING AND IGNORE
      ELSE
        name1 = Strextract(cName,"","/")
        name2 = Strextract(cName,"/","")
        cName1 = Strextract(name1,",") +" "+ ;
          Strextract(name1,"",",")
        cName2 = Strextract(name2,",") +" "+ ;
          Strextract(name2,"",",")
        Replace ARTISTTEMP With cName1 +"/"+cName2
      ENDI
    ELSE
*      Single Name
      IF "," $ ARTISTTEMP
        cName1 = Strextract(cName,",") +" "+ ;
          Strextract(cName,"",",")
      ELSE
        cName1=ALLTRIM(ARTISTTEMP)
      ENDI
      Replace ARTISTTEMP With cName1
  ENDIF
ENDSCAN

Lee

Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk

 

[Imaginecorp]

Glad I could help...

 

[mmerlinn]

keepingbusy & Imaginecorp

ATXRIGHT() is a UDF. See FAQ184-5975 for code for the ATXLEFT() & ATXRIGHT() UDFs.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[mmerlinn]

{b}keepingbusy[/b]

SUPERMAN RETURNS / (WS DUB SUB AC3 DOL)
PIRATES OF CARIBBEAN: DEAD MAN'S CHEST / (WS DUB)

These point out a shortcoming in the ATXRIGHT() & ATXLEFT() UDFs. 'SUPERMAN RETURNS / (WS DUB SUB AC3 DOL)' is returned as 'SUPERMAN RETURNS SUPERMAN RETURNS/(WS DUB SUB AC3 DOL) (WS DUB SUB AC3 DOL)'.

I have corrected the problem with the UDFs and will upload the corrected ones soon.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Imaginecorp]

mmerlinn

These functions are pretty cool... Good Job

 

[keepingbusy]

Hi mmerlinn

Thank you for pointing that out and whilst I ran with the suggestion from Imageincorp, your posts are well worthy of a star from me and anyone else findng this thread.

Lee

Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk