Need help with steps for dup check

[garyzak]

Hello;
hope to get a little help. Have two source files I need to check for duplicates within Visual Foxpro 9. Below are the steps I need to impliment and below that is the actual code. I am close but need help with the code to make sure this will accomplish what I need.
I appreciate any help.

* 1. Setup excel headers (Source doc)
* 2. Save excel as dbf (Table #1)
* 4. Export Exceed to excel
* 5. Convert Exceed excel to dbf (Table # 2)
* 6. Scan for dups
* 7. Export to excel (Table # 1)
* 8. Import into Exceed

*SET EXCLUSIVE OFF
SET DEFAULT TO c:\temp
*getfile()
*IMPORT FROM "c:\temp\source doc

2005-2006.xls" TYPE xls
SCAN all
STORE fname TO cFname
STORE lname TO cLname
STORE SLNAME TO cSLFname
STORE SPOUSE TO cSPOUSE
STORE ORGNAME TO cORGNAME
STORE jobtitle TO cjobtitle
STORE COMPANYDIV TO cCOMPANYDIV
STORE ADDRES1 TO cADDRES1
STORE ADDRES2 TO cADDRES2
STORE city TO ccity
STORE state TO cstate
STORE zip TO czip
STORE phone TO cphone
STORE phtype TO cphtype
STORE phnotes TO cphnotes
STORE county TO ccounty

SELECT 2
LOCATE FOR lname = cLname

IF FOUND()
MESSAGEBOX("Duplicate Found")
SELECT 1
delete
* do something
IF NOT FOUND()
* do something

SELECT 1
endscan

 

[Mike Lewis]

Hi Garyzak,

need help with the code to make sure this will accomplish what I need.

My first reaction is: Have you tested the code? That will tell you much better than we can if it will do what you need.

Having said that, I will make a couple of suggestions.

First, you do not seem to be opening any tables. You're obviously using two tables; one of them is the one you have just imported. It's not at all clear what the other one is.

Second, do you really want to do a message box every time you find a duplicate. The message box tells the user nothing about which record was duplicated. I can imagine someone sitting there mindlessly clicking OK until you eventually finish the scan.

It would be much better to write the details of the duplicates to a separate file, or a report, or even display them on the background screen. That way, the user will have a list to work on. Or, just delete the dupes, if that is what you want.

Finally, here is an alternative approach you might consider:

SELECT * FROM Table1 WHERE lName IN ;
(SELECT lName FROM Table2) INTO CURSOR csrDupes

When this code finishes, csrDupes will contain a list of all the duplicated lNames.

Or, the following code will just delete all the dupes without ceremony:

DELETE FROM Table1 WHERE lName IN ;
(SELECT lName FROM Table2) INTO CURSOR csrDupes

Hope this helps.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[garyzak]

Thank you Mike;
one file is exported from a contact managment program called Exceed, the other file will be an Excel file sent by user. I must export the Exceed file, change to a .dbf and check it for dups against the user supplied Excel, that will also be changed to a .dbf.

 

[JRB-Bldr]

Once you get the 2 data tables open, I assume that you have a field which is in common with both tables.

You might want to look at thread184-1320953

His issue was just slightly different than you want.
Obviously you would need to build an Index on one of the tables using the common field as your Index expression.

He wanted to eliminate the Orphans which were not in both tables.
You can easily modify the suggestions to eliminate the duplicate records which are in both tables.

Good Luck,
JRB-Bldr

 

[garyzak]

Thank you both. I will rework with your suggestions. Thank you

 

[garyzak]

Hello again;
Thanks for the earlier replies. I am close to having this working but still a bit off. Please let me know if you have any ideas.
When I open the program and chose a "source" Excel file to match against MERGE, it goes through each record with the messagebox. The record that pops up in the messagebox as a duplicate will not delete when I click yes to delete. Also I am trying to put together code at the end that will exit out if no dups are found.
Can you please take a look? Greatly appreciated...


*SET EXCLUSIVE OFF
* 1. Setup excel headers (Source doc)
* 2. Save excel as dbf (Table #1)
* 3. Export Exceed to excel
* 4. Convert Exceed excel to dbf (Table # 2)
* 5. Scan for dups
* 6. Export to excel (Table # 1)
* 7. Import into Exceed


CLOSE database

SET SAFETY OFF

SET DELETED on

clear

Set Default To c:\temp

cstr = Alltrim(Getfile('Excel:xls'))

Import From (cstr) Type Xls

*store ALIAS() TO cTableName

STORE JUSTFNAME(cstr) TO cFileName

STORE getwordnum(cFileName,1,".") TO cTableName

use

*IMPORT FROM c:\temp\+&cstr TYPE xls

SELECT 2

IMPORT FROM c:\temp\merge.xls TYPE XLS

SELECT 1

USE "c:\temp\"+(cTablename)+".dbf" exclusive

alter table (cTablename) rename column b to fNAME

alter table (cTablename) rename column c to lNAME

alter table (cTablename) rename column e to addres1

GOTO 1

delete

SCAN all

STORE ALLTRIM(fname) TO cFname
STORE ALLTRIM(lname) TO cLname
STORE ALLTRIM(addres1) TO cAddres1
STORE "Source: "+cFname+" "+cLname+CHR(13)+cAddres1 TO cSource

SELECT 2

LOCATE FOR ALLTRIM(b) = cLname AND ALLTRIM(c) = cFname

IF FOUND()

STORE "Destination: "+b+" "+c TO cDestination

eMessageTitle = "Duplicate Checker"

eMessageText = "Duplicate found. Source Record:" +cSource+CHR(13)+CHR(13)+"Destination Record:"+cDestination+CHR(13)+CHR(13)+ "Would you like to delete source record?"

nDialogType = 3 + 16 + 256

* 4 = Yes and No buttons

* 16 = Stop sign icon

* 256 = Second button is default



nAnswer = MESSAGEBOX(eMessageText, nDialogType, eMessageTitle)



DO CASE
CASE nAnswer = 2

cancel

CASE nAnswer = 6

SELECT 1

delete

CASE nAnswer = 7

SELECT 1

ENDCASE

endif

*code for no dups


endscan

 

[JRB-Bldr]

If you read over the other recommended posting you will notice Mike Yearwood's comment:

pay attention to the fact that jrbbldr is not doing
SELECT 1
He is doing
SELECT CUSTOMER
It is way more readable and easily maintainable, especially if you come back to this in a few months.

Additionally look over the VFP Help file for
HELP "ALTER TABLE – SQL Command"
I don't see any RENAME COLUMN option listed

Lastly are you familiar with running your code in VFP's Trace Window? If so you could follow the code execution line-by-line to see what is happening.

Good Luck,
JRB-Bldr

 

[garyzak]

Thank you for the reply and information, JRB-Bldr and Mike. I have this working, it is deleting dups when I chose yes and passes when I chose no. The last thing I could use a bit of advise with if at all possible is how to EXPORT to Excel and convert the file into both a .csv (the file is to be imported into a program called Exceed-contact management), and it must be in the format(acceptable columns) that Exceed will accept for Import.
Sorry for the multiple posts but I am learning on the fly. I really appreciate all help and this is a great experience as I dive into VFP!
Here are a few examples of column headings Exceed will accept:
AIDNO(exceed creates primary number)
LNAME
FNAME
SLNAME
SPOUSE
ORGNAME


*SET EXCLUSIVE OFF
* 1. Setup excel headers (Source doc)

* 2. Save excel as dbf (Table #1)

* 4. Export Exceed to excel

* 5. Convert Exceed excel to dbf (Table # 2)

* 6. Scan for dups

* 7. Export to excel (Table # 1)

* 8. Import into Exceed

CLOSE database
SET SAFETY OFF
SET DELETED on
clear
Set Default To c:\temp
cstr = Alltrim(Getfile('Excel:xls'))
*Alltrim..Removes all leading and trailing spaces or parsing characters from the specified character expression
Import From (cstr) Type Xls
*store ALIAS() TO cTableName
STORE JUSTFNAME(cstr) TO cFileName
STORE getwordnum(cFileName,1,".") TO cTableName
* Getwordnumreturns a specified word from a string
use
*IMPORT FROM c:\temp\+&cstr TYPE xls
SELECT 2
*this is the MERGE from Exceed
IMPORT FROM c:\temp\merge.xls TYPE XLS
SELECT 1
USE "c:\temp\"+(cTablename)+".dbf" exclusive
alter table (cTablename) rename column b to fNAME
alter table (cTablename) rename column c to lNAME
alter table (cTablename) rename column e to addres1
GOTO 1
DELETE
STORE 0 TO ncount
STORE 0 TO nduplicates
SCAN all
STORE ncount +1 TO ncount

STORE ALLTRIM(fname) TO cFname
STORE ALLTRIM(lname) TO cLname
STORE ALLTRIM(addres1) TO cAddres1
STORE ALLTRIM(f) TO CCITY
STORE ALLTRIM(G) TO CSTATE
STORE ALLTRIM(H) TO CZIP

STORE "Source: "+cFname+" "+cLname+CHR(13)+cAddres1+", " +CCITY+", "+CSTATE+", "+CZIP TO cSource

SELECT 2

LOCATE FOR ALLTRIM(b) = cLname AND ALLTRIM(c) = cFname

IF FOUND()

store nduplicates+1 to nduplicates

STORE "Destination Record: "+ALLTRIM(c)+" "+ALLTRIM(b)+CHR(13)+ALLTRIM(i)+", " +ALLTRIM(k)+", "+ALLTRIM(l)+", "+ALLTRIM(m) TO cDestination

eMessageTitle = "Duplicate Checker"
eMessageText = "Duplicate found: "+csource+CHR(13)+CHR(13)+;
cDestination+CHR(13)+CHR(13)+ "Would you like to delete source record?"

nDialogType = 3 + 16 + 256

* 4 = Yes and No buttons

* 16 = Stop sign icon

* 256 = Second button is default

nAnswer = MESSAGEBOX(eMessageText, nDialogType, eMessageTitle)

DO CASE
CASE nAnswer = 2

cancel

CASE nAnswer = 6

SELECT 1

*this will delete record from data session
DELETE

CASE nAnswer = 7

SELECT 1

ENDCASE
ENDIF

*code if no dups

ENDSCAN
eMessageTitle = 'No Duplicates'
eMessageText = 'Completed Search. '+STR(ncount, 4)+' Records Searched'+Chr(13)+Chr(13)+STR(nduplicates, 4)+" Duplicates Found"

nDialogType = 0 + 16 + 256
* 4 = Yes and No buttons
* 16 = Stop sign icon
* 256 = Second button is default

nAnswer = MESSAGEBOX(eMessageText, nDialogType, eMessageTitle)

PACK
*EXPORT TO ???

 

[JRB-Bldr]

Keep in mind that when you say "EXPORT to Excel and convert the file into both a .csv" you are describing two distinctly different file formats. Yes, Excel can open a CSV, but it is not a 'true' Excel file.

To "EXPORT to Excel" in the most simplistic manner

mcExcelName = "C:\Temp\Export.xls"
* --- Export to Excel File Format (not CSV) ---
Select MyTable
COPY TO (mcExcelName) XL5

A more complext manner would be to have your VFP use Excel Automation and write the individual cell values, cell format, calculation formulas, etc.

To "convert to CSV"

mcCSVName = "C:\Temp\Export.csv"
* --- Export to Excel File Format (not CSV) ---
Select MyTable
COPY TO (mcExcelName) DELIMITED

Good Luck,
JRB-Bldr

 

[garyzak]

Yes,
the Exceed program only accepts .csv so I am trying to automate this with VFP. It needs to export the .csv and use the column headers in my previous post. Can the exported file include the column headings, or would I have to do this manually?

 

[Mike Lewis]

Gary,

COPY TO Exceed.CSV TYPE CSV

This will include the field names in the first row.

COPY TO Exceed.TXT TYPE DELIMITED

This gives you a file that you can open in Excel, but without the field names.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[garyzak]

That saved the file as you mentioned as a csv. Thank you very much! One last question, please.
Where can I define the correct column headers for Exceed:
ex:
AIDNO(exceed creates primary number)
LNAME
FNAME
SLNAME
SPOUSE
ORGNAME
This is what was in the new Exceed file:
a fname lname d addres1 f g h i
Thank you for helping my...

 

[JRB-Bldr]

If you look into your VFP Help utility for HELP "ALTER TABLE – SQL Command" you can see the options available.

Apparently you are already successfully renaming DBF file fields (example: alter table (cTablename) rename column b to fNAME, etc.)

So my question is what is the problem?
1. The field name?
2. The field sequence?

If the field name, then use additional RENAME commands like you have already done.

It the field sequence, you can modify the export's COPY TO command by including the FIELD option (see VFP Help system for HELP copy to) and output the fields in the order desired.

Good Luck,
JRB-Bldr

 

[garyzak]

Great! I modified and it is now working. Thank you for your help.