468,771 Members | 1,500 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,771 developers. It's quick & easy.

Trying to import from Excel sheet but order gets messed up

Hi again

I am trying to import from an excel spreadsheet into access 2002, but the
order that is in the spreadsheet is not preserved when I import. using
DoCmd.TransferSpreadsheet in code.

Access sets indexes on certain fields with "Code" in the field. Even if I
remove these index, the order is close, but still some of the fields are out
of order.

There is no true order to this spreadsheet, and it must stay in this order.

What can I do?

Thanks again
Nov 13 '05 #1
3 5420
On Sat, 02 Oct 2004 15:42:46 GMT, "Danny" <da********@hotmail.com>
wrote:

So it should stay in this "no true order", huh?

I would add a column: LineNumber, and import that as well. Then in
Access you can sort by that column.

I think the Auto-Index feature of Access is mostly more of a
hindrance. Turn it off in Tools/Options/Tables-Queries/AutoIndex.

-Tom.
Hi again

I am trying to import from an excel spreadsheet into access 2002, but the
order that is in the spreadsheet is not preserved when I import. using
DoCmd.TransferSpreadsheet in code.

Access sets indexes on certain fields with "Code" in the field. Even if I
remove these index, the order is close, but still some of the fields are out
of order.

There is no true order to this spreadsheet, and it must stay in this order.

What can I do?

Thanks again


Nov 13 '05 #2
Simple solution. Add an autonumber primary key to your table and
you're done. The PK will determine the sort order, and will force the
records to remain in import order. I think you're making a
fundamental mistake about database tables - they're *inherently
unordered*. If you want them ordered, apply a filter or open a query
based on the table... In Excel you can do something like this:

A3=A2-1
(This record's value equals the value of the previous record minus
one.) Doing that in Access, you need to specify a sort order as there
is no inherent record order in your table...
Nov 13 '05 #3
pi********@hotmail.com (Pieter Linden) wrote ...

Your reply is slightly confused:
Simple solution. Add an autonumber primary key to your table and
you're done.
You are assuming the table currently has no primary key, which would
be unusual (technically, it would be a heap rather than a table <g>).
You could've simply suggested adding a IDENTITY/autonumber column, but
why make it the primary key?
I think you're making a
fundamental mistake about database tables - they're *inherently
unordered*.


I think you meant to say, 'if no ORDER BY clause is specified, their
order is not guaranteed'. In practice, the 'default' order will be the
table's physical order (i.e. order on disk) which is determined by the
clustered index. For Jet, the primary key is the clustered index but
new rows are only physically reordered when the database is compacted.

Rather than rely on coincidence and default behavior, it would be
better to add an explicit row ID to the Excel data and ensure this
imported into the database.

Jamie.

--
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Matthew Wieder | last post: by
10 posts views Thread by Niklas | last post: by
2 posts views Thread by madeleine | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.