Solved Alternative to Transferspreadsheet (1 Viewer)

[tmyers]

What would be the best method to import an Excel file into a table aside from docmd.transferspreadsheet? The main reason I am avoiding it this go around is that you cant guarantee the order in which it will write to the table and I need to maintain the order of the data.

I know one simple solution would be to open the file and write a sequence via looping to the rows then import the file and sort by that newly added column but wasn't sure if there was maybe a better method I could use to maintain the structure of the file.

Thanks in advance!

 

[theDBguy]

Database tables are not really stored in any specific order. If you want to view the data in any order, you will typically use a query to do that.

 

[tmyers]

Database tables are not really stored in any specific order. If you want to view the data in any order, you will typically use a query to do that.

I assumed as much and figured adding a sort of "helper" field that I can sort the data by regardless of the order in which it was appended to the table would likely be the way to go.

 

[LarryE]

If you order your EXCEL range first and then use DoCmd.TransferSpreadsheet it should transfer over in order. Make sure you have a Primary Key established in your table before you do the transfer. Have you tried it?

 

[MajP]

I am with @LarryE. I do not think I ever had a problem where the transfer or other import methods did not import in the order of the Spreadsheet. However as stated make sure the table has an autoNumber field to ensure you can get it out in order it was imported in.

 

[tmyers]

The method I had used in the past was just a simple transferspreadsheet and while it would work most of the time, about 1 out of 6 or so imports it would append it in a full garbled mess.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblTempImport", strfile, True

This is what I have been using. Like I said above, it would mess it up only once every handful of times. I figure to be safe, I could add that helper into the file and temp table but it wouldn't make its way to the final table as I would be able to sort by the autonumber field at that point.

 

[ebs17]

maintain the structure of the file

Why don't you stay in Excel if you don't want to change anything?
Do you think it would make sense to rebuild Excel in Access?

If you are serious about it, you first develop your database schema, completely detached from any Excel stuff or even from sequences. When the schema is in place and functional, you can think about importing data. Not before.

 

[The_Doc_Man]

Not that I want to discourage you, but even if you imported your data in the exact order, the first time you did a bulk update or insert, your "perfect" order would be gone. Logically, because they are based on Set Theory, tables have no inherently obvious order. (Queries using viable keys with an ORDER BY have a definite order.) Tables appear in the order of most chronologically recent change LAST. If you think about it, importing a sorted list from front to back changes the table in exactly that order, so initially the table seems to still be sorted. For a "live" database, Updates, Inserts, and Deletes all tend to be more or less random. The table order will eventually reach that state. A query with an ORDER BY would present it in any way you wanted, and a Compact & Repair will rewrite the table in Prime Key order. And that is all you really need to understand about table order in Access.

 

[MajP]

Not that I want to discourage you, but even if you imported your data in the exact order, the first time you did a bulk update or insert, your "perfect" order would be gone.

Yes but the OP stated they would have an autonumber in the destination table so that is not an issue.

I would then simply link to the spreadsheet and build a sorted append query sorted on the "helper" sort column. The records will append in order and the autonumber will retain the sort order.

 

[LarryE]

The method I had used in the past was just a simple transferspreadsheet and while it would work most of the time, about 1 out of 6 or so imports it would append it in a full garbled mess.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblTempImport", strfile, True

This is what I have been using. Like I said above, it would mess it up only once every handful of times. I figure to be safe, I could add that helper into the file and temp table but it wouldn't make its way to the final table as I would be able to sort by the autonumber field at that point.

I also import EXCEL data into a temporary table, then transfer it to the data tables. I found it most helpful if the table names in the temp import table and the column heads in the EXCEL file match exactly. From there it is easy to use an Append action query to move the data over to the data tables. I do not use a Primary Key in the temp import table. There is no need. Also, make sure the data formats in the EXCEL file match the Data Types in the temp import table. Sometimes EXCEL formats and ACCESS Data Types and formats can be a problem. More than likely, that is why you get inconsistant import results.

But as Doc indicated above, you are certainly going to have to use ACCESS queries to order the data anyway once it is transferred to your data tables from your temp import table. No getting around that.

 

[tmyers]

Correct. I am just trying to make sure it ends up in my destination table correctly as I can then set the data to sort via the autonumber field in my queries. Previously I had a field where the user could input the order in which they wanted to sort by was received overwhelming feedback on how much everyone hated it. This database is much more...optimized? I think thats the word I am looking for so I may not run into the same problem as I had before.

I am putting together the module now and will run various test to see what happens before I go adding things to it.

 

[gemma-the-husky]

Has this been said? Add a column to the spreadsheet with a number for the sort order. Then it will also be in the imported data. Whether you actually need it is a different question.

 

[The_Doc_Man]

Yes but the OP stated they would have an autonumber in the destination table so that is not an issue.

Just so long as the OP understands that even the autonumbers get out of order after updates. Not that they change, but rather, if you open the table directly, the autonumbers are in no better order than the rest of the table. Which is why you need queries when the word "ORDER" is in play. Having an autonumber guarantees only that you CAN impose order, not that you HAVE imposed order. Only a query with an amenable sequencer field will assert a particular order. MajP, you know that - but some newer users do not appreciate that fact.

 

[tmyers]

Just so long as the OP understands that even the autonumbers get out of order after updates. Not that they change, but rather, if you open the table directly, the autonumbers are in no better order than the rest of the table. Which is why you need queries when the word "ORDER" is in play. Having an autonumber guarantees only that you CAN impose order, not that you HAVE imposed order. Only a query with an amenable sequencer field will assert a particular order. MajP, you know that - but some newer users do not appreciate that fact.

I understand what you are saying Doc . How the table itself looks when viewed, is of no consequence as I know it can get all messed up but as long as the data gets inserted into the table sequentially, that will enable me to take my queries for a form and sort by the autonumber field.

In the end, it doesnt matter what the autonumber itself is as long as the data was inserted in the correct order, each subsequent record should have a higher number so it will still sort as desired.

 

[theDBguy]

I understand what you are saying Doc . How the table itself looks when viewed, is of no consequence as I know it can get all messed up but as long as the data gets inserted into the table sequentially, that will enable me to take my queries for a form and sort by the autonumber field.

In the end, it doesnt matter what the autonumber itself is as long as the data was inserted in the correct order, each subsequent record should have a higher number so it will still sort as desired.

Pardon me but I may be missing something. I have always been given the analogy that inserting data into a table is somewhat similar to dropping marbles in a bucket. So, even if you drop the marbles in perfect order, you will still be looking at a mess when you peek into that bucket.

Now, if you labeled each marble with a sequence number, then you can certainly pull them out of the bucket one at a time, also in perfect order.

Not sure if this is helpful or not but just wanted to mention it.

 

[CJ_London]

I use a sql query rather than transferspreadsheet - something like

SELECT *
FROM (SELECT * FROM [sheet1$A:D] AS xlData IN 'C:\path\filename.XLSX'[Excel 12.0;HDR=no;IMEX=1;ACCDB=Yes])  AS XL;

or for a full sheet

SELECT *
FROM [excel 12.0 xml;hdr=yes;imex=1;DATABASE=C:\path\filename.XLSX].[Sheet1$A:D] AS T;

both of the above can be converted to an append query and linked to other tables if required. (e.g. left join to exclude records already imported)

You can get issues with datatyping (as you can with transferspreadsheet) but easily resolved with criteria and or cdate/cint etc functions as part of the query.

Benefits are no VBA required (unless you want to make it dynamic), You can also specify a range so does not need to start at A1

e.g. use
[sheet1$C10:F50]

 

[The_Doc_Man]

Pardon me but I may be missing something. I have always been given the analogy that inserting data into a table is somewhat similar to dropping marbles in a bucket. So, even if you drop the marbles in perfect order, you will still be looking at a mess when you peek into that bucket.

No, when dropping records in the table, they attach onto the end of the working space in the chronological order they were dropped. IF IT HAPPENS that they were in order when dropped, they will be in order AND STAY THAT WAY until the first time you update records, insert new records with random key values, or delete some records.

This happens because Access makes a new copy of the record and threads it to the back of the table and at the same time unthreads the old copy. It has to do it that way because of the possibility that you could roll back a transaction or sequence. Also because if the table is being shared, someone else might have that record open and it can't be deleted ... then. When you open a recordset in Dynaset mode (among others), you lock the current set of records briefly. It is thus possible for the person who opened the Dynaset to not see the changes to the table UNLESS they included dbSeeChanges in the OpenRecordset call. And the other users don't HAVE to do that, it IS an option.

 

[theDBguy]

No, when dropping records in the table, they attach onto the end of the working space in the chronological order they were dropped. IF IT HAPPENS that they were in order when dropped, they will be in order AND STAY THAT WAY until the first time you update records, insert new records with random key values, or delete some records.

This happens because Access makes a new copy of the record and threads it to the back of the table and at the same time unthreads the old copy. It has to do it that way because of the possibility that you could roll back a transaction or sequence. Also because if the table is being shared, someone else might have that record open and it can't be deleted ... then. When you open a recordset in Dynaset mode (among others), you lock the current set of records briefly. It is thus possible for the person who opened the Dynaset to not see the changes to the table UNLESS they included dbSeeChanges in the OpenRecordset call. And the other users don't HAVE to do that, it IS an option.

Did not know that. Thanks, Doc!

 

[tmyers]

That is very informative Doc, but also way over my head . I get the idea of what you are saying though.

 

[Gasman]

That is very informative Doc, but also way over my head . I get the idea of what you are saying though.

So in essence, it does not matter how you load the table, as long as you have a method to obtain the data in the order you would like?