transferspreadsheet doesn't go in order??? (1 Viewer)

[overclockgirl]

I am transfering chunks of data from Excel into Access tables using the transferspreadsheet command. I will be looping through many XL files and creating and/or updating approx. 35 Access tables from each XL.
The XL files have a specific order to them. When I transfer the specified range to Access, only some of them stay in this order.
After doing a google search, I found that this is a "rare and sporatic" thing that happens.
Does anyone know of a good solution to keep my data in the correct order?

 

[petehilljnr]

The easiest way (depending on the type of data) would be to create the order by using a query based on that table.
In other words - don't worry about the order in the table- just work with the data through the queries. This may involve you creating a ordering column in the spreadsheet before it gets imported.

 

[Moniker]

Always use a query to view the data in your table. As has been mentioned a lot in here, a table stores your data in no particular order. Yes, you can open a table and sort it and filter it and so on, but those are literally query views of the table, not the order in which the data is stored.

It's very easy. Make a query and drag every field individually (not the asterisk -- * --) in to the QBE. Set your sorts and filters from there on the fields where it is appropriate.

 

[MossleyMike]

Running the sort after the "import" seems the most sensible way - but maybe the "sort sequence" required is not a logical sort (numeric, alphabetic etc).

I've just tried this today - run the "TransferSpreadsheet" routine but use acLink rather than acImport. I set up a table in the database (say "T_Import") and set the link to this table - the link came across as "T_Import1" No worries - I then do my processing against "T_Import1" - which is in the same order as the original spreadsheet - because it IS the original spreadsheet

Convoluted - but seems to work 100% of the time ....

 

[gemma-the-husky]

overclockgirl

access sorting will not necessarily be in any order - the point is that access is not excel - you deal with and process "sets" of data, and logically a set is a collection of homegenous data that therefore does not require to be sorted

you can impose any arbitrary sort order you require

hence the point is that access is not working incorrectly - if you are interested in reconstructing the order, and a column to the spreadsheet with a line count, and access will store that for you - ie your original order

 

[MossleyMike]

Twa always a shame with TransferSpreadsheet - it can handle a row of headers but not a column of row numbers !

Must have a word with Bill.....

 

[gemma-the-husky]

mike - thats the point - there are no row numbers in access! - its not what a relational database is!

 

[MossleyMike]

Don't get me wrong - I'm with you all the way - it's just that the majority of users "at our place" believe spreadsheets to be the answer to life, the universe and everything, and regularly worship at the Church of St Excel. I just spend too much time using Access as a "staging post" , writing interfaces between spreadsheets and other (mostly Oracle) dbs - and quite often sort order can be an issue....

If I want a good ol' flat file, I have to put my Lotus Notes developer hat on

 

[bignose2]

I know a really old thread but thanks for the LINK instead of Import advise.

Not quite sure why people always think we should not expect data to be imported in the same order of an original source, just seems odd, shy does it have to change, if you want to change it then you do a sort or alter as required.

I have come across this before by my example now it imported bank statements, can of course sort on the day, but no time provided as is normal, when there are numerous on the same day, you would expect & want the last of that day to have the end of day balance but if it is all mingled up takes some working out, so there is a very good reason to import in the said order.

I have saved & used as csv for this very reason which is a bit crazy when excel already has the data nicely formatted.

 

[The_Doc_Man]

A lot of folks who observe this phenomenon fail to realize that Excel cells are individually separate entities that go where you put them and stay that way until you move them - like notes on cork board with push-pins. Whereas data in Access is more like that pile of notes taken down from the cork board and tossed into a bucket. Then, every now and then, someone shakes the bucket or roots around in the bucket.

When you use Access to then look into the bucket, what order WOULD you expect? But fortunately, Access CAN order the bucket if you tell it to. Here's the trick: You never ACTUALLY look in the bucket. You ask access to show you a datasheet view or a query's view of what is IN the bucket. And at that point, the view you chose COULD impose a different order anyway.