• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

Importing from Excel to Access 2007

I have a Access 2007 DB that I am having an issue importing an Excel sheet.

The main issue is that the Excel sheets top header row doesnt the Access table field names.

If I manualy select all rows and columns from the Excel sheet and paste them into the Access table it works fine but I have a macro button that I want to use to import. I get errors when I try to use my macro because the field names dont match. when I change the Excel field names to match the Access table field names the macro works fine.

So, I get this excel import once a week. Is there a way to fix the macro so that I can import the excel data without having to always change the field names?
0
CMILLER
Asked:
CMILLER
3 Solutions
 
omgangCommented:
You can have your macro import the Excel data into a new/blank table then run an insert/append query to insert all the records from the new/blank table into the target table in Access.  To create the query, first you must import the Excel data into a new table.  Then create an insert query and match up the Excel table fields with the Access table fields.  Once you have this insert/append query working you simply modify your macro to run the import first and then the insert/append query second.

OM Gang
0
 
mbizupCommented:
An approach I generally take when importing data is to import the Excel file into a temporary "staging table"  and then run Update or Insert queries and or code to get the imported data into the required format in my actual Access tables.
0
 
Dale FyeCommented:
I agree with mbizup, and always use a staging table.

The problem with importing Excel data is that Excel generally allows users to put any value they want in a cell, so in a column that should all contain numbers, the user can inadvertently insert an string value, which will screw up your import.

I generally link the Excel table to the Access FE, then import the data from Excel into my Access staging table.  Generally, every column in my staging table will be of a Text or Memo datatype.  This will ensure that any value entered in Excel will import into the staging table.  I then run through a series of data validity checks and tag rows in the staging table that contain invalid data.

I then present the list of invalid rows to the user to fix, if they choose not to fix the row, it remains in the staging table.

When they choose to import the valid rows, I run a query that explicitly types the values in each column to the correct datatype for the field in the destination table.
0
 
CMILLERAuthor Commented:
Thanks, it worked out well doing it the way you suggested.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now