Access 2007: Import and Export Data
Access provides options for both importing and exporting data in certain file formats. Before trying these procedures, it is strongly recommended you make a backup copy of your database for protection against damaged data. See a summary of file formats Access can import or export.
Import Data
Two ways you can retrieve data from other sources include importing and linking. Importing allows you to translate data from a foreign format into the Access database format and then add it to a new table or append it as new records to an existing table. Linking creates a temporary relationship between the external data and Access. While the data remains in its original location, Access does allow you to perform some manipulation. When, working with two Access databases, note the source (external database) table structure cannot be edited in the destination (linking database).
To import data from an external source:
- Open the Access database you want to use.
- Select the External Data tab.
- Begin importing data by selecting the tool from the Import group
that matches the file format to retrieve:
[Import
Access Data button]; 
[Import
Excel Data button]; 
[Import
Share Point List button]; 
or 
.
Alternatively, click 
to
view a pop-up menu containing additional formats. - Specify the data source by typing the path in File
name or by clicking
and navigating to the
file location. - Indicate how and where the data should be stored in the destination database by clicking the appropriate radio button (e.g., Import into a new table; Append to a table; or Link to the data source.
- Respond to the remaining prompts as they appear. The steps you see depend on the data format you selected.
Export Data
You can export data in a variety of formats including an Excel file, a SharePoint List, and a Word or text file. The process of exporting a table requires reorganizing the data to work with the new format. Certain Access data types such as AutoNumber, Yes/No, Memo, and OLE often cause problems in other programs. You may need to manipulate this type of data to get it to work the way you want. As a general rule, keep field names short and use only letters and numbers for Access fields. This will help the fields to conform with general rules for field names.
To export data to an external source:
- Open the Access database you want to use.
- Select the External Data tab.
- Begin exporting data by selecting the tool from the Export group that
matches the file format:
[Export
Excel Data button]; 
[Export
SharePoint List button]; 
or 
.
Alternatively, click 
to
view a pop-up menu containing additional formats. - Respond to the remaining prompts as they appear. The steps you see depend on the data format you selected.
If you select the Save import steps or the
Save export steps check box during the import/export process, you can access
and reuse these settings later.
For imports, click 
[Save
Imports button] in the Import group on the Ribbon.
For exports, click 
[Save
Exports button] in the Export group on the Ribbon.