importing from excel into my database.

[mattpearcey]

I have my database set up, with around 700 records in. I have been given an excel spreadsheet by a collegue that contains similar data that i want to import.

How can i do this easily without causing too many problems with duplication errors. Is it a case of an append query? or something different?

the problem is, that the spreadsheet has, in many occasions, the same record on many rows, but they contain different data. so will this import ok? Thank you for your help already.

Matt Pearcey

 

[Terpsfan]

Access imports data from Excel very seemlessly. Within Access go to the menu, pick File then Get External Data. Then in the Files of Type box pick the version of Excel you're using to import. Then you just select the fields you want to import from the spreadsheet. The wizard will ask you if you want to import this information to an existing table or to create a new table. Since your table is already set up, then select that table and then import. This will add news rows to your table from the spreadsheet.

 

[mattpearcey]

Will it recognize any duplicates that may be imported? And what about cases where there are say one record (ABC Bank), then lots of different data n multiple rows. So, will this be picked up as one record, or will it error due to it seeing a duplicate? I feel i would have to do a little manual importing on these records, or maybe changing the excel doc before importing. Thank you for your help already.

Matt Pearcey

 

[mymou]

The wizard will not pick up on duplicates (unless the properties in the table do not allow duplicates - and this will cause import errors).

Each row in Excel will form one record in Access. It may require some manipulation in Excel to get the data into the exact format you require - but that should be too difficult (lots of silly looking formula). Alternatively, try to do the same in Access (bit more difficult - but more interesting - do a search on 'duplicates' on this forum and see what you get!)

In general, create some temp tables and see what happens.

Stew

 

[PhilipSchroevers]

If you don't want the duplicates, make sure that the tabe properties do not allow them (a primary key is sufficient). Then use the wizard. It will NOT accept the duplicates, but it will accept all the other records. The records that were NOT accepted are put in the table ImportErrors.

If the table DOES allow duplicates (I can hardly imagine that) and you cannot modify that (not even temporarily), create a temporary table (or an temporary database!), import th ereocrods usng th ewizard and then use the Duplicate records wizard to detect the duplicates.