Import, export, and link data between Access and Excel

Important notice for users of Office 2003    To continue receiving security updates for Office, make sure you're running Office 2003 Service Pack 3 (SP3). The support for Office 2003 ends April 8, 2014. If you’re running Office 2003 after support ends, to receive all important security updates for Office, you need to upgrade to a later version such as Office 365 or Office 2013. For more information, see Support is ending for Office 2003.

You can share data between Access and Excel in many ways. You can copy data from an open worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) and paste it into an Access datasheet, import a worksheet into an Access database, or simply load an Access datasheet into Excel using the Analyze it with Excel command. This topic explains in detail how to exchange data between Access and Excel by importing, exporting, or linking to data.

In this article


Import data from Excel

If your goal is to store some or all data in one or more Excel worksheets in Access, you should import the contents of the worksheet into a new or existing Access database. When you import data, Access creates a copy of the data in a new or existing table, without altering the source Excel file.

Here are some common scenarios where you might want to import Excel data into Access:

  • You are a long-time user of Excel, and you are now thinking of moving to Access. You might start by importing your Excel worksheets into one or more new Access databases.
  • You are a manager who receives reports in Excel format from your employees. You want to merge the reports with an existing database to update the contents of the database.
  • You are a manager, and your employees send you weekly reports as Excel files. You want to automate the import operation, to save time.
  • You are developing an application where you need to write code to import Excel data into Access.

If this is the first time you are importing data from Excel, you should know the following:

  • Do not look for a way to save an Excel worksheet as an Access database. Microsoft Excel does not provide "save as" functionality to create an Access database with Excel data.
  • To start the import operation, an Access database must already be open. If you want to import the data into a new database, you must create a blank database (that does not contain any tables, forms, or reports) before starting the importing operation.
  • Data that is imported from a worksheet is stored in a new or existing table in the current database. If you are not familiar with tables or how a database is structured, see About tables (MDB) and About designing a database.
  • When you open an Excel worksheet 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 worksheet instead of importing its data. Linking to a worksheet is different from importing a worksheet into a database. For more information about linking, see the section Link to data in Excel, later in this topic.
Getting started with the import operation

ShowStep 1: Identify the data that you want to import

Start by locating the Excel file, and select the worksheet that has the data you want to import to Access. You can import only one worksheet or named range (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) at a time during an import operation. If you don't want to import the entire worksheet or an existing named range, you might want to consider creating a new named range that has only the cells that you want to import. Note that during the import operation, you can choose to skip one or more columns in the worksheet or named range, but you can't skip rows.

To import multiple worksheets or named ranges, repeat the import operation for each worksheet or range.

For more information on working with named ranges, see Excel Help.

ShowStep 2: Identify the destination database and table

Identify the database where the imported data will be stored. If you don't want to store the data in any of your existing databases, create a blank database. For more information about how to create a blank database, see the section "Create an empty database without using the Database Wizard" in the topic Create an Access database.

You will start the import operation after you open the destination database. During the operation, you will be prompted to specify the name of the table that will store the imported data. If you specify the name of an existing table, Access will append the data to the table. If you choose to store the data in a new table, Access will create the table during the import operation.

ShowStep 3: Preparing the data for the import operation

Review the data that you want to import, and do the following:

  • Make sure the cells are in tabular format. If the range includes merged cells, the contents of the cell will be placed in the field that corresponds to the leftmost column, and the other fields will be left blank.
  • 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 there are no column names in the worksheet, or a specific column name violates the field naming rules in Access, Access will assign a valid name to each corresponding field.
  • If you are importing the data into an existing table, make the sure the number of columns in the worksheet or named range matches the number of fields in the table. The name, date type, and position of each column must also match those of the corresponding field in the table.

Tip    To review the fields in a table and their properties, open the table in Design view.

ShowStep 4: Things you should know before importing certain data types and elements

  • Graphical elements    Graphical elements, such as logos, charts, and pictures will not get imported.
  • Data type    By default, Access scans the first 25 rows to guess the data type of the column. If Access encounters values beyond the 25th row that are not compatible with the chosen data type, it will simply ignore those values and not import them.

 Note   You cannot change the data type of the destination field during the import operation.

  • Calculated values    The results of a calculated column or cells are imported and stored in a field of an appropriate data type. The formula itself is not imported.
  • Hyperlinks    A column containing hyperlinks will be imported as a text field.

After the data has been imported, you might find that not all data got imported, or that Access did not assign the right data type. See step 6 for more information about how to take corrective action.

ShowStep 5: Start the import operation

  1. Open the destination database. Make sure the database is not read-only, and that you have permissions to make changes to the database.

If the database does not exist, create it. For more information about how to create a blank database, see the section "Create an empty database without using the Database Wizard" in the topic Create an Access database.

  1. On the File menu, point to Get External Data, and then click Import.

 Note   The Get External Data command is available only when a database is open.

  1. In the Import dialog box, in the Files of type box, select Microsoft Excel.

 Note   If you do not see Microsoft Excel in the Files of type box, it is because the path to the required driver in the registry is invalid. For more information about how to correct this problem, see Could not find installable ISAM error message.

  1. Click the arrow to the right of the Look in box, select the drive and folder where the worksheet file is located, and then double-click its icon.

 Note   If you see an error message that indicates that Access cannot import the selected file, it could be either because the file is corrupt, or the data is not in a format that Access can read. Open the file in Excel and make sure the data is intact, and is in tabular format.

  1. Follow the directions in the Import Spreadsheet Wizard dialog boxes.

ShowIf the wizard doesn't start

This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.

For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later.

For more information about sandbox mode, see the Office Online article About Microsoft Jet Expression Service sandbox mode.

 Note   If you choose to import data into a new table and find that you cannot specify field options for any field other than the first, you need to obtain an update to correct a known issue. Visit the Microsoft Product Support Services site for more information about how to obtain this update.

When the import operation is complete, you will see a message that informs you of the status of the operation. If there were errors, Access creates an error log table in the database and displays the name of the table in the message.

ShowStep 6: Review the imported data and the error log table and take corrective action

The last step is to open the destination table in Datasheet view and then in Design view, and make sure the data was imported without any errors.

Reviewing the error log table    If the message that was displayed at the end of the operation mentioned errors, you might want to open the error log table and review the errors. The table has three fields — Error, Field, and Row. Each row contains information about a specific error, and the contents of the Error field should help you troubleshoot the problem. For a complete list of error strings and hints about how to troubleshoot each type of error, see the section "I imported a file and Access says it created a Tablename$_ImportErrors table" in the Troubleshoot importing and linking topic.

Reviewing the destination table    While the error messages and the error log table will inform you of most errors, it might still be a good idea to review the imported data. If you find that not all rows or columns were imported, try to identify and correct the problem in the source file, then try importing again. If data appears truncated in a column, try increasing the width of the column in Datasheet view before trying other options.

When you locate a specific problem, correct it in the source file. When you think you have corrected all known problems, repeat the import operation.

For detailed troubleshooting information, see Troubleshoot importing and linking.

Verifying and changing the data type of fields    Access attempts to assign the appropriate data type to imported fields, but you should check your fields to make sure that they are set to the data type you want. For example, in an Access database, a phone number or postal code field might be imported as a Number field, but should be changed to a Text field in Access, because it is unlikely that you will perform any calculations on these types of fields. You should also check and set field properties, such as formatting, as necessary.

Other ways to bring Excel data into Access

In addition to importing, you can use the following techniques to bring data from Excel into Access:

Top of Page Top of Page

Link to data in Excel

If you don't want to maintain a copy of the data in your Access database, you can instead link to the Excel worksheet. Linking lets you connect to data in Excel without importing it, so that you can view the data in Access. You can update the data in the worksheet when you are working in Excel and your changes will be shown when you view the linked table in Access.

When you link to a worksheet or a named range, Access creates a new table that is linked to the source cells. Linking has an advantage over importing — when you change data in the Excel worksheet, the linked table in Access updates to reflect those changes.

If this is the first time you are linking to an Excel worksheet, you should know the following:

  • You cannot create a link to an Access database from within Excel.
  • You cannot link Excel data to an existing table in the database. When you create a link, 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.
  • A database can have multiple linked tables.
  • Any change that you make to the data in Excel will be automatically reflected in the linked table.
  • When you open an Excel worksheet 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 blank database, and automatically starts the Link Spreadsheet Wizard.

For more information on linking, see About linking data and database objects.

Getting started with the link operation

ShowStep 1: Identify the data that you want to link

Start by locating the Excel file, and the worksheet that has the data that you want to link to. You can link to only one worksheet or named range at a time during a link operation. If you don't want to link to the entire worksheet or an existing named range, you might want to consider creating a named range that has only the cells that you want to import.

To link to multiple worksheets or named ranges, repeat the link operation for each worksheet or range.

For more information about working with named ranges, see Excel Help.

ShowStep 2: Identify the destination database and table

Identify the database where you want to create the link. If you don't want to store the link in any of your existing databases, create a blank database. For more information about how to create a blank database, see the section "Create an empty database without using the Database Wizard" in the topic Create an Access database.

You will start the link operation in Access after opening the destination database. During the operation, Access will create a linked table. If a table with the name you specify already exists, Access overwrite that table.

ShowStep 3: Preparing the data for the link operation

Review the data that you want to link, and do the following:

  • Make sure the cells are in tabular format. If the range includes merged cells, the contents of the cell will be placed in the field that corresponds to the leftmost column, and the other fields will be left blank.
  • 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 link operation. If there are no column names in the worksheet, or a specific column name violates the field naming rules in Access, Access will assign a valid name to each corresponding field.

ShowStep 4: Things you should know before linking to certain data types and elements

  • Graphical elements    Graphical elements in an Excel worksheet, such as logos, charts, and pictures, will not be visible in Access.
  • Data types    You cannot change the data type of the fields in the linked table. You also cannot change the size of the fields.
  • Calculated values    The results of a calculated column or cells are displayed in the table, but you will not be able to modify the values.
  • Excel column with values longer than 255 characters    During linking, if Access encounters values in Excel that are longer than 255 characters, they are stored in a memo field that displays only the first 255 characters. The workaround for this limitation is to import the worksheet or named range, instead of linking to it.

After the linked table has been created, you should open it in Datasheet view to see that the table looks exactly like the source file. See step 6 for possible errors and how to take corrective action.

ShowStep 5: Start the link operation

  1. Open the destination database. Make sure the database is not read-only, and that you have permissions to make changes to the database.

If the database does not exist, create it. For more information about how to create a blank database, see the section "Create an empty database without using the Database Wizard" in the topic Create an Access database.

  1. On the File menu, point to Get External Data, and then click Link Tables.

 Note   The Get External Data command is available only when a database is open.

  1. In the Link dialog box, in the Files of type box, select Microsoft Excel.

 Note   If you do not see Microsoft Excel in the Files of type box, it is because the path to the required driver in the registry is invalid. For more information about how to correct this problem, see Could not find installable ISAM error message.

  1. Click the arrow to the right of the Look in box, select the drive and folder where the worksheet file is located, and then double-click its icon.

 Note   If you see an error message that indicates that Access cannot link to the selected file, it could be either because the file is corrupt, or the data is not in a format that Access can read. Open the file in Excel and make sure the data is intact, and is in tabular format.

  1. Follow the directions in the Link Spreadsheet Wizard dialog boxes.

ShowIf the wizard doesn't start

This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.

For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later.

For more information about sandbox mode, see the Office Online article About Microsoft Jet Expression Service sandbox mode.

When the link operation is complete, you will see a message that informs you of the status of the operation.

ShowStep 6: Review the linked table and take corrective action

The last step is to open the table in Datasheet view and review the displayed data.

Look for strings that start with "#", such as #Num. For example, Access displays #Num in a numeric field when it encounters a non-numeric value.

Look for strings that appear truncated. Increase the width of the column in Datasheet view, but if you still don't see the entire value, it could be because the value is longer than 255 characters. Access only displays the first 255 characters, so the workaround is to import the data, instead of linking to it.

For detailed troubleshooting information, see Troubleshoot importing and linking.

Top of Page Top of Page

Export Access data to Excel

Often, you will come across situations where your data is stored in Access, but you need to move or copy it to Excel. For example, you might want to distribute your report to a group of users who prefer to view it in Excel. Or, you might want to analyze your Access data by taking advantage of the analysis features of Excel. In such situations, export the contents of your Access database to an Excel worksheet.

You can export the following to Excel:

  • The data in a table, query, form, or report.

The following illustration shows how the Catalog report in the Northwind Traders sample database will look after it has been exported to Excel:

The Catalog report in Excel

  • All or some of the rows and columns in a datasheet view

The following illustration shows how a portion of the Employee table in datasheet view will look after it has been exported to Excel:

The Employees table in Excel

 Note   You cannot export multiple objects in a single export operation. However, you can merge the data in different worksheets and worksheets after completing the individual export operations. For more information, see Merge workbooks and Move (cut) or copy cells or cell data in Excel Help.

Getting started with the export operation

ShowStep 1: Identify the data that you want to export

Start by locating the database and the object in the database that has the data you want to export. You can export a table, query, form, or a report. For example, you can export the customer data stored in the Customers table, or the entire Products catalog report.

 Note   You cannot export data access pages (data access page: A Web page designed for viewing and working with data from the Internet or an intranet. Its data is typically stored in an Access database.), macros, and modules.

ShowStep 2: Decide where to start the export operation

You can export an object from the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), or when it is open in a view. The following table describes how the view impacts what is exported.

 Note   You cannot export data from Design view or SQL view.

Object View/Window What gets exported
Table, Query, Form Database window All fields and records
Table, Query, Form PivotTable and PivotChart views All fields and records in the underlying record source, irrespective of whether the fields are actually included in the view.
Table, Query, Form Datasheet view If you want to export only some of the data, you can select it, and then choose to export only the selected data. You also have the option of exporting the entire datasheet.
Form Form view All fields and records in the underlying record source, irrespective of whether the fields are actually included in the view.
Report Database window, Print Preview, and Layout Preview All the data contained in the text boxes in the Group Header and Detail sections, and any text box in a group footer containing an expression with the Sum function. Access uses the outline feature of Excel to format the report in Excel. For more information about how a report is exported to Excel, see How reports are output to Microsoft Excel. For more information about how to work with the report in Excel, see Topics on working with outlines in Excel Help.

ShowStep 3: Identify the destination file for the export operation

During the export operation, you will be prompted to specify the name of the destination file. If a file with the name you specify doesn't exist, a new file will be created. If the file exists, one of the following things will happen:

  • If you are exporting a table or query and you don't select the Save Formatted check box during the export operation, the file will not be overwritten. A new worksheet will be added to the file with the same name as the object that is being exported. If a worksheet already exists with that name, Access will prompt you to either replace the contents of the corresponding worksheet, or specify a different name for the new sheet.

Selecting the Save Formatted check box makes the worksheet inherit the format settings similar to the datasheet, but overwrites the existing contents of the worksheet.

  • If you are exporting a form or a report, the file will always be overwritten. All of its existing worksheets will be removed, and a new worksheet with the same name as the exported object will be created.

ShowStep 4: Things you should know before exporting certain data types and controls

ShowStep 5: Start the export operation

  1. If the object you want to export is not open, in the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), click the name of the object. To save only a part of a datasheet, open the datasheet and select that portion of the datasheet before continuing.
  2. On the File menu, click Export.
  3. In the Save as type box, click Microsoft Excel 5-7 or Microsoft Excel 97-2003.

 Note   If you do not see Microsoft Excel in the Files of type box, it is because the path to the required driver in the registry is invalid. For more information about how to correct this problem, see Could not find installable ISAM error message.

  1. Click the arrow to the right of the Save in box, and select the drive or folder to save to.
  2. In the File name box, enter a name for the file (or use the suggested name).
  3. Select the Save formatted check box.
  4. Do one of the following:
    • If you are saving a datasheet, click Export All to save the entire datasheet or Save Selection if you selected a portion of the datasheet in step 1.
    • For all other database objects, click Export.

ShowStep 6: Review the Excel worksheet

Open the worksheet and make sure the data got exported completely. Look for error indicators on cells (green triangles) or error values (strings beginning with "#", instead of the data). For more information on troubleshooting error indicators and error values, see Excel Help.

While reviewing the worksheet for errors, also look for blank or missing columns, and empty cells. If you find major problems, correct them in the source database, and repeat the export operation.

For detailed troubleshooting information, see Troubleshoot exporting in Access.

Other ways to bring Access data into Excel

In addition to exporting, you can use the following techniques to bring data into Excel from Access.

  • Cut or copy data from Access and paste it into an Excel worksheet. For more information about how to do this, see the section "Copy or move records or data from multiple fields in Microsoft Access to another application" in the topic Copy or move data.
  • Export data by using code. You can write a macro or a Visual Basic for Applications (VBA) procedure to export data programmatically. For more information about how to do this, see Export data programmatically.
  • Load Access data in an instance of Excel.

ShowHow?

  1. In the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), click the name of the datasheet, form, or report you want to save and load into Excel. To load only a part of a datasheet, open the datasheet, and then select that portion of the datasheet before continuing.
  2. On the Tools menu, point to Office Links, and then click Analyze It with Microsoft Excel.

Top of Page Top of Page

Troubleshooting

If you are still having problems with any of these operations, try the following suggestions. You should find the "Step 4" and "Step 6" links very useful.

  • See the "Troubleshoot exporting in Access" or "Troubleshoot importing and linking" topics in the See Also list in this topic.
  • Click the "Step 4" links in any of the three "Getting started..." sections of this topic for potential problem areas and their solutions.

ShowA common import problem — truncated data

If you find that Excel cells containing more than 255 characters are being truncated during import, see "Data Type" under Step 4: Things you should know before importing certain data types and elements, in the "Getting started with the import operation" section of this topic. This will explain that the Import Spreadsheet Wizard previews only the first 25 rows of Excel data in predicting the data type to assign to the field in the new table.

If you have a cell that contains 440 characters in the 36th row of data, for example, Access will not see the data in that row, and, instead of creating a field with the Memo data type, the wizard will create a field with the Text data type. How could you avoid this particular problem? By cutting and inserting the 36th row of data above the 25th row in the Excel worksheet. It's also important to ensure that the data in the columns of your worksheet have a consistent data type from row to row.

  • Click the "Step 6" links for hints on how to interpret the contents of the error log table in Access or the errors in the Excel worksheet you imported to.

Top of Page Top of Page

 
 
Applies to:
Access 2003