select only not empty fields from 2 databases and copy to a new one 3

[aguilar]

i have a very big database (250 fields) and another because i needed more fields. i just need to copy the not empty fields of one record on each database to a new one. any help will be appreciated. i cant solve this.
Im using foxpro for windows 2.6

thanks eaguilar

 

[aguilar]

thanks Mike,

i dont have repeated fields.

from what i see when a non empty field is found you inserted it into the target table.

the problem is that i can not create a table with all the fields in the two source tables ("insert into" needs the table created with the corresponding fields) because FPW only allows 255 fields per record and i have one source table that has about 250 fields and the other about 55.

i need to find the non empty fields in a record in both tables (they are for the same client and will never be more than 110) and copy them to a new table in one record. that means that i need to create each time the target table and the correspoding fields, then insert the data to the fields.

sorry about my english, its not my first language. And im a lawyer, nothing to do with computers.

happy hollidays

enrique aguilar

 

[JRB-Bldr]

Will the Not-Empty fields on ALL records be the same?
Or will the Not-Empty fields vary from record to record?

I assume that a RELATION can be established between the 2 data tables in some manner.

Mike's suggestion of using the AFIELDS() command will give you the field names and other field properties for the original data table.

And his suggestion of using

IF !EMPTY(evaluate(laFields[m.lnField,1])) 
    * --- Not-Empty Field Value ---
      <do whatever>
ELSE
    * --- Empty Field Value ---
      <do whatever>
ENDIF

will allow you to test if the field contents are empty or not.

You can combine these commands in a manner similar to what he has shown to create a different, new Array containing only those fields and their associated properties which contain Not-Empty values.

After doing this for Data Table #1, continue to do the same for Data Table #2 adding its Not-Empty field information to the same new Array.

You should end up with a single new array which contains all of the field properties from both data tables which contain Not-Empty field values.

Then with this new Array you can use the CREATE TABLE ... FROM ARRAY command to build a new data table which includes ONLY those fields which are not-empty. At this point the new data table will have no records in it, merely the structure.

Finally you can do somethine like the following to populate the new table with the not-empty values from the original data table.

SELECT MyTable2
SET ORDER TO KeyID  && Previous Index Expression: Key_ID

SELECT MyTable1
SET RELATION TO Key_ID INTO MyTable2
SCAN
  * --- Copy Data Table Field Values To Memory Variables ---
  SCATTER MEMVAR

  * --- Add Values To New Table ---
  SELECT NewTable
  APPEND BLANK
  GATHER MEMVAR

  SELECT MyTable1
ENDSCAN

HOWEVER, if each record has different Not-Empty fields, then you will need a number of New Data Tables each with a different set of fields in them.
If that is the situation, then this approach may not be the best one to use.

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[tesar]

Enriq,
try
* union of structur
use tbl1
copy to stru stbl12 extended
use tbl2
copy to stru stbl2 extended
use stbl12
appe from stbl2
index on FIELD_NAME tag fn
dele all
* not empty fields are recalled
sele 2
use tbl1
scan
for ii = 1 to fcount()
if !empty(field(ii))
=seek(field(ii),stbl12)
reca
sele 2
endif
endfor
endscan
*
use tbl2
scan
for ii = 1 to fcount()
if !empty(field(ii))
=seek(field(ii),stbl12)
reca
sele 2
endif
endfor
endscan
go top
* cut off empties
sele stbl12
pack
* union of files
if recc() < 256
create tbl12 from stbl12
appe from tbl1
* suppose recc(tbl1) = recc(tbl2)
scan
sele tbl2
scat memvar
skip
sele tbl12
gath memvar
endscan
endif
* not tested, only for inspiration
* Tesar

 

[severach]

Is it possible to keep the record count of the two files exactly the same so there is a RECNO() one to one correspondance between files? If so then they are easy to link.

clos data
use table055
sele 0
use table250
set rela to recno() into table055

With this code, each time you move RECNO() in the first table, the second table automatically follows. This should allow the two tables to behave as one.

 

[aguilar]

an apology. i went out for the hollidays.

thank you all for your help. it was very usefull.

this is what i have done, hope it is usefull for others as well, its not finished but working and it is a start point.

SELECT 2
PUBLIC DIMENSION nwarr(1,1)
m.donde=1
m.lnfields = AFIELDS(lafields)
SCAN
	FOR m.lnfield = 1 TO m.lnfields
		IF EMPTY(EVALUATE(lafields[m.lnField,1]))
		ELSE
			*If not empty REDIMENSION ARRAY NWARR
			*AND COPY ITS ELEMENTS TO IT.
			
			DIMENSION nwarr(m.donde,4)
			nwarr(m.donde,1)=lafields(m.lnfield,1)
			nwarr(m.donde,2)=lafields(m.lnfield,2)
			nwarr(m.donde,3)=lafields(m.lnfield,3)
			nwarr(m.donde,4)=lafields(m.lnfield,4)

			*CREATE AND REDIMENSION ARRAY TO STORE DATA
			PUBLIC DIMENSION dato(m.donde)
			dato(m.donde)=EVALUATE(lafields[m.lnField,1])

			*GET READY TO REDIMENSION ARRAYS
			
			m.donde=m.donde+1
		ENDIF
	ENDFOR m.I
ENDSCAN

*DO THE SAME WITH OTHER TABLE

SELECT 1
m.lnfields = AFIELDS(lafields)
SCAN
	FOR m.lnfield = 1 TO m.lnfields
		IF EMPTY(EVALUATE(lafields[m.lnField,1]))
		ELSE
		
			*If not empty AVOID DUPLICATING THE SIMILIAR FIELD IN
			*BOTH TABLES, REDIMENSION ARRAY NWARR AND COPY ITS ELEMENTS TO IT.
			
			IF TYPE(lafields(m.lnfield,1))="C" AND lafields(m.lnfield,1)<>"EXP"
				DIMENSION nwarr(m.donde,4)
				nwarr(m.donde,1)=lafields(m.lnfield,1)
				nwarr(m.donde,2)=lafields(m.lnfield,2)
				nwarr(m.donde,3)=lafields(m.lnfield,3)
				nwarr(m.donde,4)=lafields(m.lnfield,4)

				*CREATE AND REDIMENSION ARRAY TO STORE DATA
				PUBLIC DIMENSION dato(m.donde)
				dato(m.donde)=EVALUATE(lafields[m.lnField,1])

				*GET READY TO REDIMENSION ARRAYS
				m.donde=m.donde+1
			ENDIF
		ENDIF
	ENDFOR m.I
ENDSCAN

*CREATE NEW CURSOR
CREATE CURSOR escrito FROM ARRAY nwarr
SELECT escrito
APPEND FROM ARRAY dato
BROWSE