Using Import wizard to get Excel data

[Jack Sheet]

V. newbie question this. Never done an import before and falling over at first attempt. Any help would be appreciated. The answer must be in this forum somewhere but I did a search on key words "autonumber import" as search criteria and it only came up with 2 hits of no relevance to my problem, so here goes:



I have a mature Access 2003 database that contains a table T_Clients

I also have an Excel 2003 workbook that contains just one worksheet, containing a large number of records that I wish to append to the table T_Clients. Row 1 of the worksheet contains header names that match, in order, the fields in T_Clients.

The first field in T_Clients (called ID_Clients) is the key field and has data type AutoNumber. Column A of the spreadsheet is labelled ID_Clients in row 1 but is otherwise a blank column.

Did I do that right? I suspect not, because when I run the wizard, none of the records are imported but instead a new table of error messages is created which is populated entirely by messages stating "null value in an autonumber field" referring to field ID_Clients.

I tried deleting column A of the spreadsheet but it still came up with all records lost due to key violations or something about referential integrity. This time it did not create any new table of error messages, though, so perhaps I was right to delete the ID_Clients column from the spreadsheet but there is something else wrong with the data.

One thing I have succeeded in doing is to import the spreadsheet into a new table. The data types of the fields in the new table in some instances do not match the data types of the corresponding fields in T_Clients. In most cases it seems to be because certain fields had no value for any of the imported records, ie the entire relevant column in the workbook was blank, and the imported field was assigned datatype "Text" where in T_CLients it was "yes/no". Would this be a cause of my problems?

Would I be advised to import the spreadsheet into a new table and then try to merge the tables? No sure how to do that merger (could not find it in the help system in Access).


SUPPLEMENTARY QUESTION
Some of the fields in T_Clients are of datatype Yes/No and the display control is a checkbox. The spreadsheet currently uses the boolean values True or False to reflect whether the checkbox should be ticked or blank respectively. Is that the (or a) correct way to do it? Or should I enter "Yes" or "No" in these fields, or perhaps numerical values 0 and -1?

(Sorry to be so long-winded)

[TheShabz]

I'm a bit confused. overall you sound like you want to append to your table from the excel doc. but the part about you having the autonumber field makes me think you want to add columns to it from your excel doc.

If it's the latter...
As you said your excel doc is ordered to match the autonumber field in the table. Here's what I would do. create the key column in your excel doc like you have but fill it in with the numbers (enter 1 in the first and drag for the rest)
ctrl+A
ctrl+C
go into access tables, right click, paste
rename this table as something else. we'll call it temp
go to your original table in design view and create fields (columns) for the ones you want to add, leave them blank.
create a new update query
throw both tables in
join by key
click on all the fields you wish to add from the original table
enter temp.fieldname in the "update to" field.
hit run.
youre done.

if its the former, dont worry about the autnumber field and google the "Insert Into" query (make sure you specify access sql as other systems have different syntax)

take care.

[Jack Sheet]

Hi, and thank you for taking the trouble to respond.

Perhaps it would be clearer if I upload an example of what I am trying to do, which is now attached to this response. The zip file contains two files: Destination.mdb and Source.xls
In this example there are just two records in each, but in real life there are hundreds.
I want to import the records from Source.xls into the table T_Clients in Destination.mdb so that source records are appended to T_Clients (and the existing data in T_Clients left undisturbed).

If I use the get external data wizard, both of the records in source are lost due to "key violations". Any ideas?

[Jack Sheet]

A further thought:
My second attempt was to import Source.xls into Destination.mdb as a separate table (which was successful), and then create an Append Query which would add all of the Source table onto the end of T_Clients table.

version 2 uploaded with this message.

This query still fails due to key violations. Now I'm stuck.

[Jack Sheet]

Another further thought:

I am not sure whether field names are case sensitive when Access compares source and destination fields during an import wizard or append query, but I have noticed a few fields where the capitalisation doesn't match. I corrected those discrepancies (version 3 attached) just in case it is case sensitive, but it did not solve the problem.

[Jack Sheet]

Solved it now, I think. All I needed to do was delete the primary key field from the source table. Still don't know why it would not import from Excel directly to the ultimate destination table, but it seems to work if I import it into a new table, then delete the autonumber primary key field in that table, and then append that table to the ultimate destination table.