Trying to IMPORT data from EXCEL

[homer2002]

Hi
Can anyone give me a quick solution to this problem

I'm trying to import data from Excell to Access 97

The data i'm trying to import it simple

NAME UserID
PersonA AuserId
PersonB Another UserID


I'm having troubles importing it though because the table it's
going into has a primary key (autonumber) so it won't import.

the table struct is
pk_primarykey - Autonumber
Name - Text
UserID - Text

Can someone help me

 

[shay]

What error message are you getting?

I don't see why having an autonumber field should be a problem - you just don't append anything to that field and it inserts a unique number for each new record.

shay

 

[homer2002]

I'm clicking on File/Get External Data/Import

Then I'm selecting the right Excel file.

It's a simple excel file With all the Names in column A
and all the UserID's in column B

Once i've selected the file, I choose to " Store data in existing table"


All I get is an error message -

"An error occured trying to import file "text.xls" . The file was not imported"

It doesn't let me create a blank space though, to avoid the primary key.

 

[shay]

Do your Access field names and Excel column names match exactly? I think they must for the Get External Data function to work.

Another possible solution (if matching the names isn't an option) is to import to a new (temp) table and then run an append query.

hth

shay

 

[ksaab]

Try selecting the excel table save it as a .txt file. Then go into your Access DB and import it with the text wizard. Make sure all fields are datatype "Text" when going through the Wizard process.

Once imported as a new table you can open your imported table in design view and change any datatypes to match your existing table structure.

Append the imported table to your existing one.

Make sure to "line up" the field names when you write your append query and append the correct data to the correct fields.

Don't append the pk. If it is set as an autonumber your existing table will assign each appended row its own unique number.