Compare 2 DBF files

[richard-service]

Dear FWH friends,

I have question about 2 DBFs compare.

Cust.DBF data below
NO NAME
01 ABC
02 Taipei
03 Taiwan
04 NCC
05 CNN

Bill.DBF data below
NO NAME
01 ABC
02 Taipei

So I want to delete Cust.DBF data same as Bill.DBF data(NO)
I want this below
Cust.DBF data below
NO NAME
03 Taiwan
04 NCC
05 CNN

These are my code below:
Cust.DBF->(::cDBx) Index on NO Tag Temp1
Bill.DBF->(::cDB2x) Index on NO Tag Temp2

Code: Select all  Expand view  RUN

SELECT (::cDBx)
DO WHILE (::cDBx)->(!EOF())

   cCUST_NOx  := (::cDBx)->CUST_NO

   SELECT (::cDB2x)
   SET ORDER TO "Temp2"
   (::cDB2x)->( DbGoTop() )
   SET SoftSeek OFF
   Dr_DbSeek( cCUST_NOx, "Temp2" )
   IF !EOF() .AND. !Empty(cCUST_NOx)
     IF (::cDB2x)->( DbRLock() )
       (::cDB2x)->( DbDelete() )
       (::cDB2x)->( DbUnLock() )
    ENDIF
   ENDIF
   SET SoftSeek ON
  (::cDBx)->( DbSkip() )
   cCUST_NOx := ""
ENDDO
 

But I can’t delete Cust.DBF(::cDBx) data

Any suggestion for it?

Thanks a lot.

[James Bott]

Richard,

Hmm. I have questions. Are there two fields named CUSTNO and NO in both the cust and bill file? If so, is the CUSTNO relevant in some way, or do you just want to delete all the data in the NO field from Cust file for any NO data that exists in the Bill file?

I am assuming that the "01" "02" "03" is that actual data that is contained in the NO field?

So if there is a "01" in the NO field of bill file, you just want to delete all occurrences of "01" in the customer file?

Also, I would like to make a suggestion. I'm sure you have heard it before, but, using database objects makes your code SO much easier to read and understand, and the code is usually much shorter. You never have to worry about workareas, alias references, etc., etc., etc...

James

[richard-service]

Richard,

Hmm. I have questions. Are there two fields named CUSTNO and NO in both the cust and bill file? If so, is the CUSTNO relevant in some way, or do you just want to delete all the data in the NO field from Cust file for any NO data that exists in the Bill file?

I am assuming that the "01" "02" "03" is that actual data that is contained in the NO field?

So if there is a "01" in the NO field of bill file, you just want to delete all occurrences of "01" in the customer file?

Also, I would like to make a suggestion. I'm sure you have heard it before, but, using database objects makes your code SO much easier to read and understand, and the code is usually much shorter. You never have to worry about workareas, alias references, etc., etc., etc...

James

Dear James,

Are there two fields named CUSTNO and NO in both the cust and bill file?
=>Yes
do you just want to delete all the data in the NO field from Cust file for any NO data that exists in the Bill file?
=>Yes
So if there is a "01" in the NO field of bill file, you just want to delete all occurrences of "01" in the customer file?
=>Yes
sorry,my mistake. NO = CUST_NO

I test it and no delete in CUST.dbf

[James Bott]

Richard,

OK, try the code below. I have taken a different approach; use a unique index to get a list of each of the CUST_NOs in bill.dbf, then delete all those CUST_NOs in cust.dbf.

I have not tested this code. Test it on some copies of your dbf files.

James

Code: Select all  Expand view  RUN

#include "fivewin.ch"

Function Main()

   use cust exclusive
   index on CUST_NO to temp1

   // Create index of unique cust_no's
   use bill exclusive new
   index on CUST_NO to temp2 unique

   // Delete all CUST_NO's in Cust file that
   // exist in Bill file.
   select("bill")
   go top
   do while ! bill->(eof())
      cCUST_NO:= bill->CUST_NO
      select("cust")
      seek(cCUST_NO)
      delete while CUST_NO == cCUST_NO
      select("bill")
      skip
   enddo

   ferase("temp1.ntx")
   ferase("temp2.ntx")
return nil
 

[ukoenig]

because customer and bill is a copy, only 1 record is deleted in CUSTOMER.dbf in my sample.
I added numbers in sample dbf CUSTOMER.
In browser BILL ( empty numbers ), You can add a number and test the RELATION to CUSTOMER.
I used a RELATION, to delete ALL records in a DO WHILE with the number defined in BILL.dbf
Just add the missing number in BILL.dbf and it will skip to the record with the number in CUSTOMER.dbf.
Select button < COMPARE DBF > and CUSTOMER will be cleaned from the number.



is this a possible solution ?
I can add a download-link for the sample.

Code: Select all  Expand view  RUN

DBSELECTAREA( 1 )
NET_USE ( "CUST.dbf", 3, 3, .T. )
INDEX ON (1)->CUST_NO TO CUST1

DBSELECTAREA( 2 )
NET_USE ( "BILL.dbf", 3, 3, .T. )
INDEX ON (2)->CUST_NO TO BILL1

dbSETRELATION( 1 , {|| (2)->CUST_NO}, "(2)->CUST_NO" )
...
...

// -------------- Delete ALL numbers ----------

FUNCTION COMP_DBF1()
LOCAL nNumber

DBSELECTAREA(2)
DBGOTOP()
DO WHILE !EOF()
    nNumber := (2)->CUST_NO
    IF !EMPTY( nNumber )
        // MsgAlert( (2)->CUST_NO, "Bill" )
        DBSELECTAREA(1) // RELATION to BILL.dbf with CUST_NO
        // MsgAlert( (1)->CUST_NO, "Customer" )
            IF (1)->CUST_NO = nNumber
            DO WHILE (1)->CUST_NO = nNumber
                NET_DELETE ( 3 )   
                DBSKIP(+1)
            ENDDO
        ENDIF
    ENDIF
    DBSELECTAREA(2)
    DBSKIP(+1)
ENDDO
DBSELECTAREA(1)
NET_PACK( "CUST.dbf", 1, 1 )

RETURN NIL

// ----------- Delete SINGLE Number --------

FUNCTION COMP_DBF2(number) // deletes ALL < CUST_NO > selected in BILL.dbf

DBSELECTAREA(1) // RELATION to BILL.dbf with CUST_NO
DO WHILE (1)->CUST_NO = number
    NET_DELETE ( 3 )  // trials 
    DBSKIP(+1)
ENDDO
NET_PACK( "CUST.dbf", 3, 3 ) // trials

RETURN NIL
 

Best regards
Uwe

[James Bott]

I just tested my sample code in my previous message and it worked perfectly for me.

I did make a couple of minor changes to the code, but the above sample works.

James

Code: Select all  Expand view  RUN

// Author: James Bott
// Date   : 1/29/2014

#include "fivewin.ch"

Function Main()

   use cust exclusive
   recall all // undelete all for testing
   index on CUST_NO to temp1

   // Create index of unique cust_no's
   use bill exclusive new
   index on CUST_NO to temp2 unique

   // Delete all CUST_NO's in Cust file that
   // exist in Bill file.
   select("bill")
   go top
   do while ! bill->(eof())
      cCUST_NO:= bill->CUST_NO
      select("cust")
      seek(cCUST_NO)
      delete while CUST_NO == cCUST_NO
      select("bill")
      skip
   enddo
   select("cust")
   use
   select("bill")
   use
   ferase("temp1.ntx")
   ferase("temp2.ntx")
return nil

[richard-service]

I just tested my sample code in my previous message and it worked perfectly for me.

I did make a couple of minor changes to the code, but the above sample works.

James

Code: Select all  Expand view  RUN

// Author: James Bott
// Date   : 1/29/2014

#include "fivewin.ch"

Function Main()

   use cust exclusive
   recall all // undelete all for testing
   index on CUST_NO to temp1

   // Create index of unique cust_no's
   use bill exclusive new
   index on CUST_NO to temp2 unique

   // Delete all CUST_NO's in Cust file that
   // exist in Bill file.
   select("bill")
   go top
   do while ! bill->(eof())
      cCUST_NO:= bill->CUST_NO
      select("cust")
      seek(cCUST_NO)
      delete while CUST_NO == cCUST_NO
      select("bill")
      skip
   enddo
   select("cust")
   use
   select("bill")
   use
   ferase("temp1.ntx")
   ferase("temp2.ntx")
return nil

Dear James,

It's work perfect!! Thanks a lot.

[richard-service]

because customer and bill is a copy, only 1 record is deleted in CUSTOMER.dbf in my sample.
I added numbers in sample dbf CUSTOMER.
In browser BILL ( empty numbers ), You can add a number and test the RELATION to CUSTOMER.
I used a RELATION, to delete ALL records in a DO WHILE with the number defined in BILL.dbf
Just add the missing number in BILL.dbf and it will skip to the record with the number in CUSTOMER.dbf.
Select button < COMPARE DBF > and CUSTOMER will be cleaned from the number.



is the a possible solution ?
I can add a download-link for the sample.

Code: Select all  Expand view  RUN

DBSELECTAREA( 1 )
NET_USE ( "CUST.dbf", 3, 3, .T. )
INDEX ON (1)->CUST_NO TO CUST1

DBSELECTAREA( 2 )
NET_USE ( "BILL.dbf", 3, 3, .T. )
INDEX ON (2)->CUST_NO TO BILL1

dbSETRELATION( 1 , {|| (2)->CUST_NO}, "(2)->CUST_NO" )
...
...

// -------------- Delete ALL numbers ----------

FUNCTION COMP_DBF1()
LOCAL nNumber

DBSELECTAREA(2)
DBGOTOP()
DO WHILE !EOF()
    nNumber := (2)->CUST_NO
    IF !EMPTY( nNumber )
        // MsgAlert( (2)->CUST_NO, "Bill" )
        DBSELECTAREA(1) // RELATION to BILL.dbf with CUST_NO
        // MsgAlert( (1)->CUST_NO, "Customer" )
            IF (1)->CUST_NO = nNumber
            DO WHILE (1)->CUST_NO = nNumber
                NET_DELETE ( 3 )   
                DBSKIP(+1)
            ENDDO
        ENDIF
    ENDIF
    DBSELECTAREA(2)
    DBSKIP(+1)
ENDDO
DBSELECTAREA(1)
NET_PACK( "CUST.dbf", 1, 1 )

RETURN NIL

// ----------- Delete SINGLE Number --------

FUNCTION COMP_DBF2(number) // deletes ALL < CUST_NO > selected in BILL.dbf

DBSELECTAREA(1) // RELATION to BILL.dbf with CUST_NO
DO WHILE (1)->CUST_NO = number
    NET_DELETE ( 3 )  // trials 
    DBSKIP(+1)
ENDDO
NET_PACK( "CUST.dbf", 3, 3 ) // trials

RETURN NIL
 

Best regards
Uwe

Dear Uwe,

Welcome share your idea sample link file. Thanks a lot.

[ukoenig]

Richard,

Like You can see, some more things are added to the test.
( I Still have to test using MULTIPLE index )

I added two RELATION-options
// Set a relation from BILL into CUST
DBSETRELATION("CUST", {|| ("BILL")->CUST_NO}, '("BILL")->CUST_NO')
and
// Set a selective relation from BILL into CUST
ORDSETRELATION("CUST", {|| ("BILL")->CUST_NO}, '("BILL")->CUST_NO')

The normal preview with NO relation selected ( all records ) :



Activated relation.

Browser CUSTOMER shows the POSITION of a selected CUST_NO.
NO seek needed :



Browser CUSTOMER shows only a selected CUST_NO like a FILTER.
NO seek needed :

[richard-service]

Richard,

Like You can see, some more things are added to the test.
( I Still have to test using MULTIPLE index )

I added two RELATION-options
// Set a relation from BILL into CUST
DBSETRELATION("CUST", {|| ("BILL")->CUST_NO}, '("BILL")->CUST_NO')
and
// Set a selective relation from BILL into CUST
ORDSETRELATION("CUST", {|| ("BILL")->CUST_NO}, '("BILL")->CUST_NO')

The normal preview with NO relation selected ( all records ) :



Activated relation.

Browser CUSTOMER shows the POSITION of a selected CUST_NO.
NO seek needed :



Browser CUSTOMER shows only a selected CUST_NO like a FILTER.
NO seek needed :

Dear Uwe,

Nice job.

[ukoenig]

Richard

completed :

viewtopic.php?f=3&t=28048

best regards
Uwe

[James Bott]

Richard,

I do wonder why you had this situation in the first place. Hopefully, this was a one time issue. Ideally, the data entry system should never allow data to be entered that causes this kind of problem. If this is a regular problem, then maybe we could offer some suggestions to prevent it from happening.

James