Import contact data in Fox Pro 7.0 from MS Word 1

[SMCmtg]

Hi FoxPro expert,

I'd like to import contact data, last name, first name, address, CSZ.

Put the data into a FoxPro table.

Phil
Sermac

 

[baltman]

How are the pieces of information stored in Word?

It could be as simple as creating a table in VFP, saving the data in Word as a text file (comma or tab delimited etc.) and then using the "append from" commnd.

If you post an exaple of how the data is in word, I'm sure we could help you out.

Brian

 

[SMCmtg]

Brian:

We have a text document with a list of names and addresses. . .about 20 on a page. . .50 pages.

We would like to import into a table with fields named: cfname,cmi,clname,caddress,ccity,cstate,czip.

Smith, John J. and Jane J.
5631 Front Porch Ave
Denver, CO 80112

Any feedback is deeply appreciated!

Phil

 

[baltman]

This isn't pretty and it isn't well commented (but it's almost 1am!). As long as the spacing doesn't get too screwed-up in posting this should work. I tried to take care of data irregularity that might exist such as zip+4, no initials, and initials without a "." after them, but I can't solve for everything given only one example. Also, I assumed just one name would be OK since you only asked for one f/l name field.

The chr(48) to chr(57) is looking for a number in the data and assumes that if there isn't one that it has found a name... so it'll fail on "the 3rd".

At the end of the day, with data like what I assume you have, you'll need to check everything manually...

I suggest saving the original information for each address in an accompanying memo field.

Let me know if this helps
Brian

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

SET SAFETY off
CREATE TABLE addtype (alldata c(180), addnumber n(5), datatype c(15), cfname c(30),cmi c(1), clname c(30),caddress c(50),ccity c(30),cstate c(15),czip c(10))
APPEND FROM sourcedata.txt TYPE sdf

sele addtype
scan
IF ALLTRIM(alldata)+" "#" " AND (CHR(48) $ alldata or CHR(49) $ alldata or CHR(50) $ alldata or CHR(51) $ alldata or CHR(52) $ alldata or CHR(53) $ alldata or CHR(54) $ alldata or CHR(55) $ alldata or CHR(56) $ alldata or CHR(57) $ alldata)=.f.
REPLACE datatype WITH "Name"
endif
endscan
GO 1
BROWSE nowait

VarCnt=1
DO WHILE EOF()=.f.
LOCATE FOR datatype="Name" AND RECNO()>=VarCnt
IF EOF()=.f.
SKIP 1
VarFrst1=LEFT(alldata,1)
IF VAL(VarFrst1)>=1 AND VAL(VarFrst1)<=9999
REPLACE datatype WITH &quot;CityStateZip&quot;
endif
REPLACE datatype WITH &quot;StreetAdd1&quot;
SKIP 1
VarLast4=RIGHT(ALLTRIM(alldata),4) && in case you have zip+4

IF VAL(VarLast4)>=1 AND VAL(VarLast4)<=9999
REPLACE datatype WITH &quot;CityStateZip&quot;
endif

IF VAL(VarLast4)<1
REPLACE datatype WITH &quot;StreetAdd2&quot;
SKIP 1
VarLast4=RIGHT(ALLTRIM(alldata),4) && in case you have zip+4
endif

IF VAL(VarLast4)>=1 AND VAL(VarLast4)<=9999
REPLACE datatype WITH &quot;CityStateZip&quot;
endif
endif
VarCnt=VarCnt+1
ENDDO


GO 1
SCAN
DO CASE
CASE DATATYPE=&quot;Name&quot;
REPLACE clname WITH SUBSTR(ALLDATA,1,ATC(&quot;,&quot;,ALLDATA)-1)

IF OCCURS(&quot;.&quot;,ALLDATA)>0 && checks &quot;.&quot; after for initial
REPLACE cfname WITH SUBSTR(ALLDATA,ATC(&quot;,&quot;,ALLDATA)+1,ATC(&quot;.&quot;,ALLDATA)-ATC(&quot;,&quot;,ALLDATA)-2)
REPLACE cmi WITH SUBSTR(ALLDATA,ATC(&quot;.&quot;,ALLDATA)-1,1)
endif

IF OCCURS(&quot;.&quot;,ALLDATA)=0 AND OCCURS(&quot; &quot;,ALLTRIM(substr(alldata,ATC(&quot;,&quot;,ALLDATA)+1,100)))>0&& checks &quot;.&quot; after for initial and if there isn't one...
REPLACE cfname WITH SUBSTR(ALLTRIM(substr(alldata,ATC(&quot;,&quot;,ALLDATA)+1,100)),1,ATC(&quot; &quot;,ALLTRIM(substr(alldata,ATC(&quot;,&quot;,ALLDATA)+1,100)))-1)
REPLACE cmi WITH RIGHT(ALLTRIM(alldata),1)
ENDIF

CASE Datatype=&quot;StreetAdd1&quot;
REPLACE caddress WITH ALLTRIM(alldata)

CASE datatype=&quot;StreetAdd2&quot;
SKIP -1
Var4Add=ALLTRIM(alldata)
SKIP 1
REPLACE caddress WITH Var4Add+&quot;, &quot;+ALLTRIM(alldata)

CASE Datatype=&quot;CityStateZip&quot;
REPLACE CCity WITH left(alldata,ATC(&quot;,&quot;,ALLDATA)-1)
REPLACE CState WITH ALLTRIM(SUBSTR(alldata,ATC(&quot;,&quot;,ALLDATA)+2,3))
REPLACE CZip WITH ALLTRIM(SUBSTR(alldata,ATC(&quot;,&quot;,ALLDATA)+5,100))
ENDCASE
endscan

GO 1
VarNum=0
REPLACE addnumber WITH varnum
SCAN
IF RECNO()>1 AND Datatype#&quot;Name&quot;
REPLACE addnumber WITH varnum
ENDIF
IF datatype=&quot;Name&quot;
VarNum=VarNum+1
REPLACE addnumber WITH varnum
ENDIF
ENDSCAN

SELECT dist addnumber,MAX(cfname) as cfname,max(cmi) as Cmi,MAX(clname) as clname,MAX(caddress) as Caddress, MAX(ccity) as ccity, MAX(cstate) as cstate,MAX(czip) as czip GROUP BY 1 FROM addtype INTO TABLE tempout

BROWSE nowait

 

[fmasin]

Hi Phil,

Brian's code above looks pretty good and should work. However, if it does not....then, see if you can transfer your contact information into excel and I can send you some code that you can use to pull information across into a VFP table.

Let me know how you get on. Thanks and regards,

Francis (f_masinde@hi-lo.co.uk)

 

[SMCmtg]

Thanks for your feedback.I got it into a table that's useable in 9 fields,
but I am trying to parse the &quot;comma&quot; off that came along with the last name. . .ie. . . Adams, Jones, Smith, etc.
How can I get rid of the comma after the name in the field &quot;cLname&quot;?
Thanks for your add'l help

Phil

 

[baltman]

replace all fieldname with chrtran(fieldname,&quot;,&quot;,&quot;&quot

Brian