Get external data vs link data results in different sequence.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a text file containing 280000+ records and when I import the file
using get external data, the sequence of the records in the table is
different from the sequence of records from the text file. When I use the
link table option, the sequence of the records in the table is the same
sequence as the text file.

Is this a bug? or is there an option to indicate that I want the resulting
table the same sequence as the text file when I do a get external data?
 
This is not unexpected. In a text file, the records are in a fixed order
from beginning to end of the file.

By contrast, the Jet database engine normally used by Access doesn't
make any promises about the order in which records are stored. If you
want to see or process the records in a particular order, you need to
use a query that sorts them into that order.
 
You can't ask Access to maintain the same sequence of records as the
text file, because relational database tables simply don't have the same
concept of sequence that is inherent in a text file.

But the only practical way to read a text file is to start at the
beginning and continue to the end, and you can be sure that the records
will be *imported* in the same order as they occur in the text file. And
if you have an Autonumber field in the table, a value is assigned to
this as each record is imported. So in general it's a fairly safe
assumption that a query that sorts the table on the Autonumber field
will return the records in the original order.

But AFAIK the Autonumber field promises to assign a unique number to
each record, but not that the numbers will *always* be sequential. So to
make absolutely sure you can add sequential line numbers to the text
file before importing the data. One way is to use the "nl" utility (in
the Windows ports of Unix utilities, free download from
http://unxutils.sourceforge.net/).
 
If he's going to be reading the text file in manually and writing each row
to the table, he could simply maintain his own row counter to add to the
table.
 
Yes. I should have suggested that as an alternative.

And if the reason for maintaining the original order is that each actual
record in the text file consists of two or more lines, line numbers
aren't the answer anyway...
 
Back
Top