Appending into a table from a query

[Albasha]

I am trying to run a query on a bunch of tables and any values I get from any table I want to put in a seperate table.

schdDate = LEFT(STRTRAN(DTOC(DATE()), "/", ""), 4)
schdDate = INPUTBOX("Enter Date:", "Enter last four digits on schedule: ", schdDate, 15000)
file_count = ALLTRIM(str(ADIR(myArray, "K:\xxx\newm\mailfile\*"+ALLT(schdDate)+"j.dbf")))
file_count=VAL(file_count)
?file_count

CLEAR
FOR nCount = 1 TO file_count

myfile = "xx/xx" + myArray(nCount,1)
?myfile

SELECT * from &myfile WHERE xxxx AND xxx" AND INTO dbf
xxxx.dbf

but the problem is that since it is in a loop it simply overwrites whatever results it had before. Any suggestions?

 

[bborissov]

Just give them new name

schdDate = LEFT(STRTRAN(DTOC(DATE()), "/", ""), 4)
schdDate = INPUTBOX("Enter Date:", "Enter last four digits on schedule: ", schdDate, 15000)
file_count = ALLTRIM(str(ADIR(myArray, "K:\xxx\newm\mailfile\*"+ALLT(schdDate)+"j.dbf")))
file_count=VAL(file_count)
?file_count

 CLEAR
FOR nCount = 1 TO file_count
   myfile = "xx/xx" + myArray(nCount,1)
   ?myfile
   new_file_name = myArray(nCount,1)+[_new]
   SELECT * from (myfile) WHERE xxxx AND xxx AND INTO dbf
   (new_file_name)
NEXT

Borislav Borissov

 

[Albasha]

Still not working - still getting a blank database


schdDate = LEFT(STRTRAN(DTOC(DATE()), "/", ""), 4)
schdDate = INPUTBOX("Enter Date:", "Enter last four digits on schedule: ", schdDate, 15000)
file_count = ALLTRIM(str(ADIR(myArray, "K:\xxx\newm\mailfile\*"+ALLT(schdDate)+"j.dbf")))
file_count=VAL(file_count)
?file_count

CLEAR
FOR nCount = 1 TO file_count
myfile = "K:\xxx\newm\mailfile\" + myArray(nCount,1)
?myfile
new_file_name = myArray(nCount,1)+[_new]
SELECT * from &myfile WHERE membrtype="P" AND Contactid="1" AND Paybillamt="0.0000" AND typeofmemb!="C" INTO dbf K:\xxx\newm\mailfile\new_file_name
NEXT

 

[baltman]

I would write something like this becase it is closer to being reusable. I took the liberty of renaming variables to conform with recommended notation. This procedure also maintains your workspaces by leaving you only with the same tables open that you started with.

Brian

MakeNewFiles([K:\xxx\newm\mailfile],[*.dbf], [_new], ;[membrtype="P" AND Contactid="1" AND Paybillamt="0.0000" AND typeofmemb!="C"])

PROCEDURE MakeNewFiles
LPARAMETERS lcDir, lcFileSkeleton, lcNewName, lcSQLFilter
PRIVATE ARRAY laOpenTables

	lnOpenTables = AUSED(laOpenTables)
	lcCurWorkSpace = ALIAS()

	IF VARTYPE(lcSQLFilter)=[L] OR EMPTY(lcSQLFilter)
		lcSQLFilter=[]
	ELSE
		lcSQLFilter = [ WHERE ] + lcSQLFilter
	ENDIF 

	lcSchdDate = INPUTBOX([Enter Date:], ;
		[Enter last four digits on schedule: ], ;
		LEFT(CHRTRAN(DTOC(DATE()), [/], []), 4), 15000)

	lnFileNcnt = ADIR(laFiles, ADDBS(lcDir) + lcFileSkeleton)

	FOR lnCnt = 1 TO lnFileNcnt 
		lcFile = ADDBS(lcDir) + laFiles(lnCnt, 1)

		lcNewFile = ADDBS(lcDir) + JUSTSTEM(laFiles(lnCnt, 1))+ ;
			lcNewName + [.]+ JUSTEXT(laFiles(lnCnt, 1))

		IF !UPPER(lcNewName)$UPPER(JUSTSTEM(laFiles(lnCnt, 1)))
			SELECT * from (lcFile) INTO TABLE (lcNewFile) &lcSQLFilter
			USE
		ENDIF 
	ENDFOR
	
	FOR lnCnt = 1 TO AUSED(laNowOpenTables)
		IF lnOpenTables>0 
			IF  ASCAN(laOpenTables,laNowOpenTables(lnCnt,1))=0
				SELECT laNowOpenTables(lnCnt,1)
				USE
			ENDIF 
		ELSE
			CLOSE TABLES
		ENDIF 
	 ENDFOR
	 
	 IF !EMPTY(lcCurWorkSpace)
	 	SELECT (lcCurWorkSpace)
	 ENDIF 
ENDPROC

 

[Mike Lewis]

Albasha,

Surely, you're still overwriting each table, except the last one.

Instead of this:

SELECT * from &myfile WHERE membrtype="P" AND Contactid="1" AND Paybillamt="0.0000" AND typeofmemb!="C" INTO dbf K:\xxx\newm\mailfile\new_file_name

maybe you need this:

SELECT * from &myfile WHERE membrtype="P" AND Contactid="1" AND Paybillamt="0.0000" AND typeofmemb!="C" INTO dbf FORCEPATH(new_file_name, "K:\xxx\newm\mailfile")

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[baltman]

Mike,

You are correct. I've never actually thought of how scope affects my naming conventions other than local vs. global. To me, the most useful thing to know is the intended data type.

At the end of the day, the most important thing is that the team of programmers on a project use and understand the same conventions.

I just wanted to suggest to Albasha that there might be a better way to name variables than s/he is using.

Brian (TechnicalUser)