Importing Data into Access from Excel

benkingery

Registered User.
Local time
Today, 00:20
Joined
Jul 15, 2008
Messages
153
I have a new Column on a table that I need to start filling in the data for existing records within my table. In order to determine exactly which records need a value for this column, I exported a query that specified which records meet the criteria of needing a value for the new column. I collected all the relevant data into excel. So I have 2 columns, 1 is the primary key of the table, and the 2nd is the New Column populated with the values I want to import into my database.

If I use the "File"-->"Get External Data"-->"Import" function, it doesn't allow me to select what items I want to import, but rather tries to create new records based on the information on the sheet. For obvious reasons, it will not import because of key violations, etc.

I imported the info into a temp table that I can use to create a query to show 3 fields (1-primary key of table, 2-New Column of table, 3-New Column of temp table). From there I can copy and paste items from the query results into the New Column in the original table, but I want to know if anyone knows how to update the data quicker.

Any thoughts are greatly appreciated
 
You should be able to import the way youwant from Excel...BUT...since you already have it in a temp table in your DB look into an "Update query" or "Append query"...... Might give you what your looking for.
 
BACKUP your database.

Use and UPDATE query.

Create a query with the two tables, better to use the main table first, but does not matter.
Join the two tables on the primary key
MENU = QUERY - UPDATE
Pull down the column you want to update (recives the data) from the main table.
In the UPDATE TOO area, place the [tablename].[columnname] of the joined table.
Save the query.
close the query.
run the query.
 

Users who are viewing this thread

Back
Top Bottom