Specifying which colums in excel should be imported into which fields in existing db

hyedipin

Registered User.
Local time
Today, 14:06
Joined
Oct 10, 2007
Messages
13
I am sure it should be right in front of me, but I can't seem to find it. I am using default Contact Management template of Access 2000, and I modified some of the fields and added a few new ones. I would like to import my own excel sheet into DB but I want to tell access, for example, to import Customer Name column to First Name field, Street Name column to Address field, etc..

Thank You in advance.
 
yeah, the way that I do this is as follows. I'm not sure if there is an easier way, there could well be.

1. Get your data in an excel spreadsheet. Add a new empty row at the top of the sheet. This will be used to add the headers.
2. Export a sample of the table (that you wish to import to) from Access.
3. Open the exported table from Access and examine the header names. Take a list of the names of all of the fields. The idea is that you "force" your data into the existing fields.
4. Go back to your own sheet with the empty header row, and label the columns with the appropriate header to match the corresponding field in the exported table. If there is no matching field in your sheet create an empty row with the same header.
5. Re-order the columns in your sheet to match those in the exported table. You should be okay for a Contacts DB, but make sure that none of the fields are formatted as dates etc etc. as this will cause a problem when importing.

Once this is done you will have your data in a format that can be directly imported into the database (or even cut and paste)
 
I understand what you are doing. Basically instead of telling access where to place each imported column, you are matching the column headers in your excel to those of Access, if there is any that you don't have on Excel, you just create them and leave them empty.

It is interesting approach. I wonder if Access 2007 or later versions have this option.

Thanks for your comment.
 
I am getting this error:

77834215rc8.jpg
 
Did you import the sheet or did you just try to copy and paste?

If you have the fields matched exactly, then there must be an difference in the types. Access must be expecting a date type and you are entering a text or something like that.

If you take a look at the table that you are importing to in design view, this should give you your answer.
 
Thanks. I think the problem was "ContactType" which should be and ID No. matching contact types. I removed that and it is good now.
 

Users who are viewing this thread

Back
Top Bottom