Select Distinct

[MrDontKnowNothing]

hi everybody,

i need to open a *.dbf (say sometable.dbf) and make a select distinct on column1 and column2 of it and write the result to a excel file *.xls (say sometable.xls).

do i need to create a new database?
how do i to open the *.dbf?

code samples are appreciated.

sorry for such a basic question. i'll try to ask more qualified questions next time!

cheers

 

[StewartUK]

The command you want is:-

SELECT DISTINCT Column1, Column2 FROM Sometable INTO CURSOR MyOtherTable

After you run this, you end up MyOtherTable so you can then issue the command:-

COPY TO Sometable.xls TYPE XL5

This creates an Excel file. It's not the most up to date version of Excel, but that doesn't really matter. If you open it in Excel and then alter & save it you'll get a message saying it's not in the most up to date format.

The SELECT automatically handles opening Sometable. INTO CURSOR creates a temporary table (the actual file is Something.tmp) that is deleted when you close VFP.

Hope that helps,

Stewart

 

[DSummZZZ]

You can only make a distinct record set on one column, unless you concantonate them. To open a table:

SELECT 0 &&... next available workspace
USE MyTable1
SELECT 0 &&... next available workspace
USE MyTable2
.
.
.

(Just as a reference, you can't open more than one table at a time using a single command, like for instance USE *.DBF)

Then:

SELECT DISTINCT Column1 FROM MyTable1 INTO CURSOR MyCursor1

For more than one column:
SELECT DISTINCT Column1 + Column2 AS ComboColumn FROM MyTable1 INTO CURSOR MyCursor1

This is assuming they are both character data types. Numeric and date will have to be converted, or they will get added together and the distinct will be of the total of the two columns:

SELECT DISTINCT STR(Column1) + STR(Column2) AS ComboColumn FROM MyTable1 INTO CURSOR MyCursor1

And so on. Does this help?


-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[GriffMG]

I'm not sure quite what you mean, but...

Distinct is generally used to isolate just one column and get the 'spread' of values within it - without duplications:

Select 0
use sometable
Select Distinct Column1 from SomeTable into cursor MyResults
Select MyResults
Copy to c:\MyXLS XL5

If you need to have the 'distinct' of two columns you're going to have to work a bit harder:

Select 0
use sometable
Select Distinct Column1+Column2 As MyDistinctBit, Column1,Column2 from SomeTable into cursor MyResults
Select MyResults
Copy to c:\MyXLS XL5

Does that help?



Regards

Griff
Keep [Smile]ing

 

[MadTown]

Dsummzzz and Griff,

Maybe I'm missing something here, according to The Hackers Guide to FoxPro

When you specify DISTINCT, every field is compared; records that exactly match another record in the set are eliminated, so that each unique combination appears only once. If this sounds slow, it's because it is slow. Since you rarely want to match up every single field, you're usually better off culling duplicates with GROUP BY

It's been a long time since I used distinct but I'm pretty sure it worked that way the Hackers guide stated.

I guess another way to do the same thing would be

Select column1,column2;
 from mytable ;
 group by column1,column2;
 into cursor myothertable

 

[GriffMG]

Madtown is absolutely right (just tested it) the distinct does apply to all listed columns.

so

Select 0
use sometable
Select Distinct Column1,Column2 from SomeTable into cursor MyResults
Select MyResults
Copy to c:\MyXLS XL5

Will work fine


Regards

Griff
Keep [Smile]ing

 

[DSummZZZ]

Well I guess I still learn every day. Maybe I was getting confused with GROUP BY.

-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm