Import from excel when data is in different columns - beginner

[faodavid]

Hi
i want to important data from an excel table but the data is not in the correct column order as my database any ideas what I need to do?

Thanks


dave

[ItsMe]

If you link to an Excel file you can use a query object to help you append records to your table(s).

Another approach is to import the excel file using a saved procedure/specification. Since the file is not structured the way you need it, your named spec can import to a Temp Table. A query object can then append records to your permanent/production tables. After the append process is complete, you can delete the records from the temp table, using the temp table the next time you call your named spec.

[faodavid]

Thanks for this - I ran a power query on the excel table, made the changes there then imported the power query table - all seems well so far - can you see any potential problems with me doing it this way?
Dave

If you link to an Excel file you can use a query object to help you append records to your table(s).

Another approach is to import the excel file using a saved procedure/specification. Since the file is not structured the way you need it, your named spec can import to a Temp Table. A query object can then append records to your permanent/production tables. After the append process is complete, you can delete the records from the temp table, using the temp table the next time you call your named spec.

[ItsMe]

I ran a power query on the excel table

If you mean that you linked to the spreadsheet and used an action query to UPDATE records in your table, this is fine. The thing that you need to be aware of is that linking to an Excel Spreadsheet will cause the Excel file to be Read Only. The best approach, when linking, is to programmatically link to Excel and then break the link after you run your Action query.

My preference is to use temp tables. I will create a copy of the production Excel file in a temp folder and then run the Import procedure on the DB's own personal copy. I suppose you could use a similar approach when linking if there is a chance of other users accessing the spreadsheet.

[June7]

Actually, a linked Excel can be edited, just not through Access. I just tested. Access linked to spreadsheet, the linked table is open, went to Excel spreadsheet, edit data, return to Access and as soon as I click anywhere on the table, it refreshed and the edits show.

I do have a vague recollection that this would not work in Access 2003 and/or 2007. Seem to remember not being able to edit spreadsheet if the Access was open.

[ItsMe]

I think the distinction I should have made is that when a DB is accessing a linked file it will be available to others in Read Only mode. Been a while since I tested it.