Video: Move data from Excel to Access

Do you have a contact or inventory list in Excel that is getting hard to manage? Maybe it’s time to move it into Access, which provides better tools for managing large amounts of data. Watch this video to see how to perform a simple import operation in Access.

 Note    This video does not cover importing data into Web databases. For more information about Web databases, see the article Build a database to share on the Web.

In the video

Prepare your data in Excel

 Important    If the following instructions require you to make changes to your worksheet, make a backup copy of the workbook or worksheet before proceeding.

  • If the data in your Excel worksheet is separated by empty rows or columns, consolidate it into a single list as much as possible. Empty cells are OK, but try to avoid completely empty rows or columns in the data you want to move.
  • As much as possible, arrange it so that each column contains the same type of data—for example, all numeric, or all text. If there is a mix of data types in a column, try to put at least one of each type in the first eight rows of the data. This will help Access decide on the best data type for that column, and will help avoid import errors.
  • Access uses the worksheet tab name as the table name. For example, if the worksheet is named Sheet1, the imported table in Access will also be named Sheet1. If you want to assign a more meaningful name for the table prior to moving the data, rename the worksheet as follows:
    • Double-click the worksheet tab (at the bottom of the worksheet), type the name that you want, and then press ENTER.

Move the data to Access

  1. In Excel, select the data that you want to move to Access, including the column headings (if any).
  2. Right-click the selected data, and then click Copy.
  3. In Access, open an existing database, or create a new one.

    If creating a new database, Access displays an empty table. Close this table without saving it.
  4. If the Navigation Pane is not displayed, press F11 to display it.
  5. Right-click anywhere in the Navigation Pane, and then click Paste.

    Access displays a dialog box asking if your data contains column headings.
  6. If the data you copied includes column headings, click Yes; otherwise, click No.

    If all goes well, Access displays a message that the object was imported successfully.
  7. Click OK to continue.

Access displays the new table in the Navigation Pane. Double-click the new table to verify that the data was imported correctly.

Fix import errors

If any problems were encountered during the import operation, Access creates an ImportErrors table that contains descriptions of the errors. To view the errors, double-click the ImportErrors table. If there were many errors, you might want to delete the imported table, fix the problems in the Excel worksheet, and then try the copy/paste procedure again. If there are just a few errors, you might prefer to fix them manually in Access. Once you have fixed any problems, you can delete the ImportErrors table.

Set field data types

If you discover that you cannot enter the type of data you want in the Access table, you might need to change the data type of the field.

To determine a field's data type, select a value in the field, and then on the Fields tab, in the Formatting group, look at the Data Type box. If, for example, the data type is Number, Access will prevent you from entering any letters in the field, such as 166A.

Change a field's data type

 Warning    In some cases, changing the data type of a field can cause data loss. Make a backup copy of the .accdb file (or of the table itself) before changing a field's data type.

  1. Click in the cell you want to change.
  2. On the Fields tab, in the Formatting group, select a new data type from the Data Type list.

    Access displays a warning message.
  3. If you have a backup copy of your data, and you are sure you want to continue, click Yes.
  4. Save the table.

Add a primary key

To prevent duplicate records, and to make it easier to create relationships between tables, we recommend that you add a primary key field to the new table. Use the following procedure.

  1. In the Navigation Pane, right-click the table and then click Design View.
  2. In the first empty cell of the Field Name column, type "ID".
  3. In the Data Type column, select AutoNumber.
  4. While the new ID row is still selected, in the Design tab, in the Tools group, click Primary Key.
  5. Save and close the table.