Import CSV data/Record order

  • Thread starter Thread starter Stephen Brooker
  • Start date Start date
S

Stephen Brooker

Hi all,
I've got a Access 2000 DB that has the option to import external data from a
CSV file. It gives the user the option of choosing a particular file, then a
VBA sub reads the file line by line, manipulates the data slightly, then
uses a INSERT command to insert the data into a temp table (permanent table
that is cleared out every time). The user can then go over the temp table
and make any changes they like before the data is transfered across to the
permanent table.
My problem is the data in the temp table has to be kept in the exact same
order as the CSV file, which it isn't. There are large chunks of data that
are perfectly in order, but the chunks themselves are all over the place.
For example, out of the last 40 or so records, 20 may be in the correct
place at the end, the other 20 may be in the middle of everything else.
There is nothing wrong with the code reading and inserting the data, so I'm
assuming it's some kind of buffering thing in Access, but I'm not sure what.
As far as I understand Access doesn't support COMMIT etc, so is there some
was to ensure that the data is written in order.

TIA

Steve.
 
Hi Stephen,

Access makes no promises about the order in which it stores or retrieves
data. If you want to display or output data in a particular order, the
*only* way is to use a query that sorts it into that order.

If there's nothing in the data that you can sort it on, you need to
provide it as you import it. Since your code is reading the file line by
line, it would be easy to have it count the lines and store the count in
an additional field in the temp table.

Otherwise I'd use Perl or the "nl" utility to add a line number to the
CSV file.
 
Hi John,
No there's nothing inherent in the data items that determines order, so I
guess I'll be adding a counter of some type.
One question however, does this apply to data transfered from one table to
another within Access, or just to data being imported as I am doing?

Thanks.

Steve.
 
Hi Stephen,

It applies throughout Access. Some people liken tables to "buckets" of
data: put something in there and you know it's in the bucket, but not
whereabouts in the bucket it is or when it will come out if you start
pouring.

Hi John,
No there's nothing inherent in the data items that determines order, so I
guess I'll be adding a counter of some type.
One question however, does this apply to data transfered from one table to
another within Access, or just to data being imported as I am doing?

Thanks.

Steve.
Click to expand...
 
Thanks John, added a counter and it now works fine.

S.

John Nurick said:
Hi Stephen,

It applies throughout Access. Some people liken tables to "buckets" of
data: put something in there and you know it's in the bucket, but not
whereabouts in the bucket it is or when it will come out if you start
pouring.
Click to expand...
 
Back
Top