retaining the sequence of large external text imports

[jonefer]

I ran into a situation which is very disturbing and was wondering if you guys
ran into it and/or have seen a patch or option to fix it.

When I import a text file into ACCESS using "get external data", data does
not load sequentially as seen in the text file. For example if I import
100000 records, I was under the impression that the table created will be in
the same sequence as the text file when no primary key is assigned except the
default which is the autonumber. I've recently found out that this is not
true, there's portions of data that does not follow the order of the text
file. The only way around it (so far) is to link the data. Linking seems to
keep the original order intact.

Am I doing something wrong here? Is there some sort of importing option or
a patch you guys know of?

I think it's volume related, the text file I use contains 280000+ records.
When I chop it down to smaller chunks, it appears to work fine.

 

[Jeff Boyce]

Access tables are data buckets, and have no inherent order (OK, there's an
order, but only Access knows/cares). If YOU wish the records to have some
inherent order, it is up to you to specify how Access is to display the
records ... but note that the actual records in the table are still stored
at Access' direction.

One way to impose order would be to have a primary key. Another would be to
use a query to retrieve the records, and use a sort value on one/more fields
(e.g., date/time).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[John Vinson]

I ran into a situation which is very disturbing and was wondering if you guys
ran into it and/or have seen a patch or option to fix it.

When I import a text file into ACCESS using "get external data", data does
not load sequentially as seen in the text file.

As Jeff says, Access tables don't HAVE an order, and you cannot count on a
table without a primary key being displayed in the order that records were
entered, or ANY particular order.

What I'd suggest is that you create an empty table with the same fields as
your text file, *plus* a sequential autonumber primary key. If you Link to
your text file and run an Append query to pull the data into the local table,
you'll have a local table in PK autonumber order (and have an indexed field
to use in Queries to sort it if desired).