Using dbase for dos, I want to thin thru a database and eliminate all records where there are less than 100 records per city. What would the syntax be? Is it possible to do this without writing a program? I'm looking for something like: Delete all for city < 100, but of course that is a datatype mismatch. The slow way is: dele all for city = "Tulsa" (but I have a list of 500 cities. Very slow.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
dbase for dos help in deleting records 1
- Thread starter barrmel
- Start date
-
1
- #2
- Thread starter
- #3
I don't believe that there are too many differences between db3+ and db4 coding so I can try to help, unless there is another member or person with db4 willing to help you.
So, that being said; "city" is a field name? And, the proceedure is something like:
1) use ThisDatabaseName
2) count the number of times that city contains "Tulsa"
3) if the count is less than 100 then delete all records that contain "Tulsa" in the city field.
4a) if not EOF(database) then find goto 2 and change "Tulsa" to "Dallas", "San Fransisco", "Portland", etc.
4b) if EOF(database) then exit program.
Is this about right?
--MiggyD It's better to have two heads to solve a problem from different angles than to have tunnel vision to a dead end.
So, that being said; "city" is a field name? And, the proceedure is something like:
1) use ThisDatabaseName
2) count the number of times that city contains "Tulsa"
3) if the count is less than 100 then delete all records that contain "Tulsa" in the city field.
4a) if not EOF(database) then find goto 2 and change "Tulsa" to "Dallas", "San Fransisco", "Portland", etc.
4b) if EOF(database) then exit program.
Is this about right?
--MiggyD It's better to have two heads to solve a problem from different angles than to have tunnel vision to a dead end.
- Thread starter
- #5
Well in 3+, you CAN use the internal editor by using "modify command <proceedurename>" at the dot prompt.
Unfortunately, this editor is so limited that I have to use Notepad or another text editor so that I can really write good codes above 1K. I know...it's really, really, really sad.
But again, is city a field or a database name?
--MiggyD It's better to have two heads to solve a problem from different angles than to have tunnel vision to a dead end.
Unfortunately, this editor is so limited that I have to use Notepad or another text editor so that I can really write good codes above 1K. I know...it's really, really, really sad.
But again, is city a field or a database name?
--MiggyD It's better to have two heads to solve a problem from different angles than to have tunnel vision to a dead end.
- Thread starter
- #7
Ok, thanks. I'll write a quick little program for you, when I get a chance. Hopefully, on Tuesday, I'll have something for you...I have to review some of my old programs.
--MiggyD It's better to have two heads to solve a problem from different angles than to have tunnel vision to a dead end.
--MiggyD It's better to have two heads to solve a problem from different angles than to have tunnel vision to a dead end.
I've tested the following. It works well with DB 3+, hopefully you won't have to modify too much. Make sure that you change the wording in [red]RED[/red] to the database you wish to modify. (note that this program will be processing a working copy...that way if there are any situations that you'll require the original data, it'll still be there.) Just to be on the safe side, make a secondary copy of the original data.
*------Start copying here------
*
* Created with assistance of MiggyD from
* Tek-Tips . com
*
set echo off
set talk off
set step off
clear
use [red]ORIGINAL.DBF[/red]
if file("CLEANUP.DBF"
THEN
delete file CLEANUP.DBF
endif
YN = "N"
? "******************************************************"
? "* This program is designed to delete records. Where *"
? "* shipments to one (1) particulare city is less than *"
? "* 100. (EX: Seattle = 100 -- not deleted) *"
? "* (EX: Dallas = 99 -- deleted) *"
? "* (EX: Tusan = 200 -- not deleted) *"
? "******************************************************"
sort to CLEANUP.DBF on City
?
? "A copy of the original database will be used incase there"
? "are any problems with the program. The copied database"
? "name is CLEANUP.DBF"
?
? "When you are satisfied with the results, please rename"
? "the original DBF file for backup purposes -AND- rename"
? "the CLEANUP.DBF file as the original one so that it can"
? "be used for processing. OR, you can delete the original"
? "but you'll still need to rename the CLEANUP.DBF file."
?
? "To terminate now, press ESC key then the 'C' key."
? "Otherwise, you can just..."
Wait to YN
clear
use CLEANUP.DBF
MYCOUNT=0
MYRECNO=RecNo()
do while .not. eof()
if eof() then
exit do
endif
CHKCITY=upper(City)
count for upper(City)=CHKCITY to MYCOUNT
if MYCOUNT<100 then
? MYCOUNT
?? " matches for " + CHKCITY
delete all for upper(City) = CHKCITY
endif
MYRECNO=MYRECNO + MYCOUNT
if MYRECNO>RecCo() then
exit do
endif
goto MYRECNO
loop
enddo
pack
close all
?
? ".....Program done...."
return
*------END copying here------
Please let me know if works or not, OR If you should have any problems.
--MiggyD It's better to have two heads to solve a problem from different angles than to have tunnel vision to a dead end.
*------Start copying here------
*
* Created with assistance of MiggyD from
* Tek-Tips . com
*
set echo off
set talk off
set step off
clear
use [red]ORIGINAL.DBF[/red]
if file("CLEANUP.DBF"
THENdelete file CLEANUP.DBF
endif
YN = "N"
? "******************************************************"
? "* This program is designed to delete records. Where *"
? "* shipments to one (1) particulare city is less than *"
? "* 100. (EX: Seattle = 100 -- not deleted) *"
? "* (EX: Dallas = 99 -- deleted) *"
? "* (EX: Tusan = 200 -- not deleted) *"
? "******************************************************"
sort to CLEANUP.DBF on City
?
? "A copy of the original database will be used incase there"
? "are any problems with the program. The copied database"
? "name is CLEANUP.DBF"
?
? "When you are satisfied with the results, please rename"
? "the original DBF file for backup purposes -AND- rename"
? "the CLEANUP.DBF file as the original one so that it can"
? "be used for processing. OR, you can delete the original"
? "but you'll still need to rename the CLEANUP.DBF file."
?
? "To terminate now, press ESC key then the 'C' key."
? "Otherwise, you can just..."
Wait to YN
clear
use CLEANUP.DBF
MYCOUNT=0
MYRECNO=RecNo()
do while .not. eof()
if eof() then
exit do
endif
CHKCITY=upper(City)
count for upper(City)=CHKCITY to MYCOUNT
if MYCOUNT<100 then
? MYCOUNT
?? " matches for " + CHKCITY
delete all for upper(City) = CHKCITY
endif
MYRECNO=MYRECNO + MYCOUNT
if MYRECNO>RecCo() then
exit do
endif
goto MYRECNO
loop
enddo
pack
close all
?
? ".....Program done...."
return
*------END copying here------
Please let me know if works or not, OR If you should have any problems.
--MiggyD It's better to have two heads to solve a problem from different angles than to have tunnel vision to a dead end.
- Status