Query a dbf file

  • Thread starter Thread starter JimmyKoolPantz
  • Start date Start date
J

JimmyKoolPantz

I want to change the order of the records in a dbf file.

I query'd a dbf file using a foxpro driver, and used the following
query statement.

qy = "SELECT * FROM " + tb + " ORDER BY SEQ_NO DESC"

Now if I run my query, and open up my dbf file, shouldn't the Seq_No
field be in Descending order?

The query executes fine, error free, however, it does not sort the
file as I have requested.

Any suggestions?
 
I want to change the order of the records in a dbf file.

I query'd a dbf file using a foxpro driver, and used the following
query statement.

qy = "SELECT * FROM " + tb + " ORDER BY SEQ_NO DESC"

Now if I run my query, and open up my dbf file, shouldn't the Seq_No
field be in Descending order?

The query executes fine, error free, however, it does not sort the
file as I have requested.

Any suggestions?
Click to expand...

What type of data is SEQ_NO? Int, varchar, something else? Also, did
the information sort or just sort wrong?

Thanks,

Seth Rowe [MVP]
 
What type of data is SEQ_NO? Int, varchar, something else? Also, did
the information sort or just sort wrong?

Thanks,

Seth Rowe [MVP]
Click to expand...

All fields in the Dbf file are "Char" type. It appears that the field
"Seq_No" did not sort at all. I also tried other fields for testing
purposes, such as the fullname field, and they did not sort either.

Thanks!
 
JimmyKoolPantz,

Are you under the mistaken impression that a Select statement's Order By
clause will actually rearrange the order of rows within a table?

Kerry Moorman
 
All fields in the Dbf file are "Char" type. It appears that the field
"Seq_No" did not sort at all. I also tried other fields for testing
purposes, such as the fullname field, and they did not sort either.

Thanks!
Click to expand...

You should see the query results sorting by Seq_No, though they be
sorted as chars and not as numbers (so things might look a bit odd).
However running a SELECT query will not rearrange anything in the dbf,
it will only effect the query results.

Thanks,

Seth Rowe [MVP]
 
You should see the query results sorting by Seq_No, though they be
sorted as chars and not as numbers (so things might look a bit odd).
However running a SELECT query will not rearrange anything in the dbf,
it will only effect the query results.

Thanks,

Seth Rowe [MVP]- Hide quoted text -

- Show quoted text -
Click to expand...

So, how would I approach this using SQL? Would I use the update
command?

I created a dbf reader and writer using the .Net binary reader and
writer classes, however, using my dbf_witer, I load everything into a
table and then write it back out to a new dbf file. I was trying to
avoid importing the file into a table, sorting it, and then writing it
back out, because some of the files we deal with are over 100,000
records. So, I decided to try and query the file.

I talked to my boss, and he said he had a similiar issue, and told me
to append a copy of the seq_no column, and change the data type to
numeric and then query it. If this is the case, I might as well just
use my dbf reader and writer class.
 
So, how would I approach this using SQL? Would I use the update
command?

I created a dbf reader and writer using the .Net binary reader and
writer classes, however, using my dbf_witer, I load everything into a
table and then write it back out to a new dbf file. I was trying to
avoid importing the file into a table, sorting it, and then writing it
back out, because some of the files we deal with are over 100,000
records. So, I decided to try and query the file.

I talked to my boss, and he said he had a similiar issue, and told me
to append a copy of the seq_no column, and change the data type to
numeric and then query it. If this is the case, I might as well just
use my dbf reader and writer class.
Click to expand...

?

The query has nothing to do with the way a table is sorted in the
database, and nor should it. In all honesty, you shouldn't care what /
how the table is sorted, the sorting should be done by the select
query or possibly the application.

Thanks,

Seth Rowe [MVP]
 
JimmyKoolPantz,

If your application depends on the physical ordering of rows in a table then
you've got real problems.

The order of rows (or columns) in a table must never be important to an
application using that data. Your app should instead work with a query result
that has been ordered.

Kerry Moorman
 
JimmyKoolPantz,

If your application depends on the physical ordering of rows in a table then
you've got real problems.

The order of rows (or columns) in a table must never be important to an
application using that data. Your app should instead work with a query result
that has been ordered.

Kerry Moorman





- Show quoted text -
Click to expand...

Maybe I mislead everyone from the beginning.

What i need to do is flip a file, an example would be, the first
record needs to be the last record of the file and the last record
needs to be the first record of the file.

Each file, that we use contains a field named "Seq_No". If I have a
dbf file that contains 5000 records the first seq_no would be 1, and
the last seq_no would be 5000.

I came here looking for the quickest way to flip the file. And, since,
some of the files are large, I was concerned about memory usage if I
decided to load the file into my application. So, I thought, the
quickest way could be just the query the dbf file. But, apparently
not. No big deal. I will just use my dbf read, store the file in
memory, sort it by seq_no, and then generate a new dbf file.

I was just exploring my options, and figured maybe I could just query
the file and call it a day.

I had to learn the dbf file structure for my dbf_writer, and i'm
really supprised that the dbf file structure does not contain a flag
position to sort columns in asc or desc order.

Also, just to let you know, the sort for this particular job is
important. Each dbf file that I generate is sent to the mail room for
printing, and some jobs require to be printed in reverse order.
 
Just to let everyone know here is what I did.

I imported the dbf file into a datatable, I appended a dummy column
named, "Seq" which has a character type, the "Seq" column contains a
sequence number, starting with 1 with padded zeros. For example, the
file I tested has 8,336 records. So the first sequence is "00001". the
last seq number is "8336". I then created a dataview and sorted the
seq field in descending order (dv.sort = "SEQ DESC". I then binded
the dataview to a dataset. (ds = dv.totable). I then exported the
dataset which generated a new dbf file. I finally queryed the dbf file
(wow, I actully used SQL!) to remove my dummy column.

Total records: 8,336
Total time: 1.8 seconds
 
Back
Top