Importing data from Microsoft Excel into Microsoft Access can save time when creating a new database or updating an existing database. Microsoft Office allows you to transfer information from Excel to Access in a user-friendly interface. It removes the need to manually enter information, reduces data entry error and ensures complete transfer of information. This feature combines the accessibility of Excel with the powerful tools of Access to let you modify, analyse and report data on your terms.
Save your Microsoft Excel spreadsheet in a format that is compatible with your Access database. If using Microsoft Access 2003, save your Excel spreadsheet in a 2003 format as well. This compatibility ensures that all of your data is preserved through import.
Click on the "External Data" tab of the ribbon at the top of the page. If the ribbon is minimised, right-click on the dark blue line and deselect "Minimize Ribbon." Under the "Import & Link" menu, select the "Excel" button to begin the import wizard.
Click on the "Browse" button and locate the Excel file you want to import. Select whether you want to import the spreadsheet into an existing or as a new table in your Access database. Choose which sheet of the Excel file you want to import.
Determine the "Primary Key" of your Excel file if you are creating a new table. This field helps the database locate records in your tables, and each record should have a unique value in its Primary Key field. If nothing is acceptable, Access can create a new primary key field.
Set the first row as column headings if you are creating a new table. If your Excel spreadsheet does not contain columns headings, you can always set them in the table's "Design View." Name your table with a strong description to locate it in the future.
If you are importing records into an existing table, make sure your destination table and Excel sheet have compatible fields and data types. Incompatibility can result in data loss.
Tips and warnings
- If you are importing records into an existing table, make sure your destination table and Excel sheet have compatible fields and data types. Incompatibility can result in data loss.