Import or link to data in an Excel workbook

Common scenarios for importing Excel data into Access

• You are a long-time user of Excel but, going forward, you want to use Access to work with this data. You want to move the data in your Excel worksheets into one or more new Access databases.

• Your department or workgroup uses Access, but you occasionally receive data in Excel format that must be merged with your Access databases. You want to import these Excel worksheets into your database as you receive them.

• You use Access to manage your data, but the weekly reports you receive from the rest of your team are Excel workbooks. You would like to streamline the import process to ensure that data is imported every week at a specific time into your database.

If this is the first time you are importing data from Excel

• There is no way to save an Excel workbook as an Access database. Excel does not provide functionality to create an Access database from Excel data.

• When you open an Excel workbook in Access (in the File Open dialog box, change the Files of Type list box to Microsoft Office Excel Files and select the file you want), Access creates a link to the workbook instead of importing its data. Linking to a workbook is fundamentally different from importing a worksheet into a database. For more information about linking, see the section Link to data in Excel, later in this article.

Prepare the worksheet

1. Locate the source file and select the worksheet that contains the data that you want to import to Access. If you want to import only a portion of a worksheet, you can define a named range that includes only the cells that you want to import.
   
   Define a named range (optional)   

   1. Switch to Excel and open the worksheet that has data that you want to import.

   2. Select the range of cells that contain the data that you want to import.

   3. Right-click within the selected range and then click Name a Range or Define Name.

   4. In the New Name dialog box, specify a name for the range in the Name box and click OK.

      Remember that you can import only one worksheet at a time during an import operation. To import data from multiple worksheets, repeat the import operation for each worksheet.

2. Review the source data and take action as described in this table.

   Element	Description
   Number of columns	The number of source columns that you want to import cannot exceed 255, because Access does not support more than 255 fields in a table.
   Skipping columns and rows	It is a good practice to include only the rows and columns that you want to import in the source worksheet or named range. Rows    You cannot filter or skip rows during the import operation. Columns    You cannot skip columns during the operation if you choose to add the data to an existing table.
   Tabular format	Ensure that the cells are in tabular format. If the worksheet or named range includes merged cells, the contents of the cell are placed in the field that corresponds to the leftmost column, and the other fields are left blank.
   Blank columns, rows, and cells	Delete all unnecessary blank columns and blank rows in the worksheet or range. If the worksheet or range contains blank cells, try to add the missing data. If you are planning to append the records to an existing table, ensure that the corresponding field in the table accepts null (missing or unknown) values. A field will accept null values if its Required field property is set to No and its ValidationRule property setting doesn't prevent null values.
   Error values	If one or more cells in the worksheet or range contain error values, such as #NUM and #DIV, correct them before you start the import operation. If a source worksheet or range contains error values, Access places a null value in the corresponding fields in the table. For more information about ways to correct those errors, see the section Troubleshoot missing or incorrect values, later in this article.
   Data type	To avoid errors during importing, ensure that each source column contains the same type of data in every row. Access scans the first eight source rows to determine the data type of the fields in the table. We highly recommend that you ensure that the first eight source rows do not mix values of different data types in any of the columns. Otherwise, Access might not assign the correct data type to the column. Also, it is a good practice to format each source column in Excel and assign a specific data format to each column before you start the import operation. Formatting is highly recommended if a column includes values of different data types. For example, the FlightNo column in a worksheet might contain numeric and text values, such as 871, AA90, and 171. To avoid missing or incorrect values, do the following: Right-click the column header and then click Format Cells. On the Number tab, under Category, select a format. For the FlightNo column, you would probably choose Text. Click OK. If the source columns are formatted, but still contain mixed values in the rows following the eighth row, the import operation might still skip values or convert values incorrectly. For troubleshooting information, see the section Troubleshoot missing or incorrect values.
   First row	If the first row in the worksheet or named range contains the names of the columns, you can specify that Access treat the data in the first row as field names during the import operation. If your source worksheet or range doesn't include the names, it is a good idea to add them to the source before you start the import operation. Note: If you plan to append the data to an existing table, ensure that the name of each column exactly matches the name of the corresponding field. If the name of a column is different from the name of the corresponding field in the table, the import operation will fail. To see the names of the fields, open the table in Design view in Access.

3. Close the source workbook, if it is open. Keeping the source file open might result in data conversion errors during the import operation.

Prepare the destination database

1. Open the Access database where the imported data will be stored. Ensure that the database is not read-only, and that you have permissions to make changes to the database.

   -or-

   If you don't want to store the data in any of your existing databases, create a blank database. To do so:

   Click the File tab, click New, and then click Blank Database.

2. Before you start the import operation, decide whether you want to store the data in a new or existing table.

   Create a new table    If you choose to store the data in a new table, Access creates a table and adds the imported data to this table. If a table with the specified name already exists, Access overwrites the contents of the table with the imported data.

   Append to an existing table    If you choose to add the data to an existing table, the rows in the Excel worksheet are appended to the specified table.

   Remember that most failures during append operations occur because the source data does not match the structure and field settings of the destination table. To avoid this, open the destination table in Design view and review the following:

   • First row    If the first row of the source worksheet or named range does not contain column headings, ensure that the position and data type of each column in the source worksheet matches those of the corresponding field in the table. If the first row contains column headings, the order of columns and fields do not need to match, but the name and data type of each column must exactly match those of its corresponding field.

   • Missing or extra fields    If one or more fields in the source worksheet do not exist in the destination table, add them before you start the import operation. However, if the table contains fields that don't exist in the source, you do not need to delete those fields from the table if they accept null values.

     Tip: A field will accept null values if its Required property is set to No and its ValidationRule property setting doesn't prevent null values.

   • Primary key    If the table contains a primary key field, the source worksheet or range must have a column that contains values that are compatible with the primary key field, and the imported key values must be unique. If an imported record contains a primary key value that already exists in the destination table, the import operation displays an error message.

   • Indexed fields    If the Indexed property of a field in the table is set to Yes (No Duplicates), the corresponding column in the source worksheet or range must contain unique values.

     Go to the next steps to run the import operation.

Start the import operation

1. The location of the import/link wizard differs slightly depending upon your version of Access. Choose the steps that match your Access version:

   • If you're using the latest version of the Microsoft 365 , Access 2021, or Access 2019, on the External Data tab, in the Import & Link group, click New Data Source > From File > Excel.

   • If you're using Access 2016, on the External Data tab, in the Import & Link group, click Excel.

   Note: The External Data tab is not available unless a database is open.

2. In the Get External Data - Excel Spreadsheet dialog box, in the File name box, specify the name of the Excel file that contains the data that you want to import.

   -or-

   Click Browse and use the File Open dialog box to locate the file that you want to import.

3. Specify how you want to store the imported data.

   To store the data in a new table, select Import the source data into a new table in the current database. You will be prompted to name this table later.

   To append the data to an existing table, select Append a copy of the records to the table and select a table from the drop-down list. This option is not available if the database has no tables.

   To link to the data source by creating a linked table, see the section Link to data in Excel, later in this article.

4. Click OK.

   The Import Spreadsheet Wizard starts, and leads you through the import process. Go to the next set of steps.

Use the Import Spreadsheet wizard

1. On the first page of the wizard, select the worksheet that contains the data that you want to import, and then click Next.

2. On the second page of the wizard, click either Show Worksheets or Show Named Ranges, select either the worksheet or the named range that you want to import, and then click Next.

3. If the first row of the source worksheet or range contains the field names, select First Row Contains Column Headings and click Next.

   If you are importing the data into a new table, Access uses these column headings to name the fields in the table. You can change these names either during or after the import operation. If you are appending the data to an existing table, ensure that the column headings in the source worksheet exactly match the names of the fields in the destination table.

   If you are appending data to an existing table, skip directly to step 6. If you are adding the data to a new table, follow the remaining steps.

4. The wizard prompts you to review the field properties. Click a column in the lower half of the page to display the corresponding field's properties. Optionally, do any of the following:

   • Review and change, if you want, the name and data type of the destination field.

     Access reviews the first eight rows in each column to suggest the data type for the corresponding field. If the column in the worksheet contains different types of values, such as text and numbers, in the first eight rows of a column, the wizard suggests a data type that is compatible with all the values in the column — most often, the text data type. Although you can choose a different data type, remember that values that are incompatible with the data type that you choose will be either ignored or converted incorrectly during the import process. For more information about how to correct missing or incorrect values, see the section Troubleshoot missing or incorrect values, later in this article.

   • To create an index on the field, set Indexed to Yes.

   • To completely skip a source column, select the Do not import field (Skip) check box.

     Click Next after you finish selecting options.

5. In the next screen, specify a primary key for the table. If you select Let Access add primary key, Access adds an AutoNumber field as the first field in the destination table, and automatically populates it with unique ID values, starting with 1. Click Next.

6. In the final wizard screen, specify a name for the destination table. In the Import to Table box, type a name for the table. If the table already exists, Access displays a prompt that asks whether you want to overwrite the existing contents of the table. Click Yes to continue or No to specify a different name for the destination table, and then click Finish to import the data.

   If Access was able to import some or all the data, the wizard displays a page that shows you the status of the import operation. In addition, you can save the details of the operation for future use as a specification. Conversely, if the operation completely failed, Access displays the message An error occurred trying to import file.

7. Click Yes to save the details of the operation for future use. Saving the details helps you repeat the operation at a later time without having to step through the wizard each time.

See Save the details of an import or export operation as a specification to learn how to save your save your specification details.

See Run a saved import or export specification to learn how to run your saved import or link specifications.

See Schedule an import or export specification to learn how to schedule import and link tasks to run at specific times.

Error strings and troubleshooting hints

Top of Page

Common scenarios for linking to an Excel worksheet from within Access

Typically, you link to an Excel worksheet (instead of importing) for the following reasons:

• You want to continue to keep your data in Excel worksheets, but be able to use the powerful querying and reporting features of Access.

• Your department or workgroup uses Access, but data from external sources that you work with is in Excel worksheets. You don't want to maintain copies of external data, but want to be able to work with it in Access.

If this is the first time you are linking to an Excel worksheet

• You cannot create a link to an Access database from within Excel.

• When you link to an Excel file, Access creates a new table, often referred to as a linked table. The table shows the data in the source worksheet or named range, but it doesn't actually store the data in the database.

• You cannot link Excel data to an existing table in the database. This means that you cannot append data to an existing table by performing a linking operation.

• A database can contain multiple linked tables.

• Any changes that you make to the data in Excel are automatically reflected in the linked table. However, the contents and structure of a linked table in Access are read-only.

• When you open an Excel workbook in Access (in the File Open dialog box, change the Files of Type list box to Microsoft Excel, and select the file you want), Access creates a blank database and automatically starts the Link Spreadsheet Wizard.