Importing & Exporting Data / Tables from

One of the most useful features of Access is its ability to interface with data from many other programs. In fact, it’s difficult to summarize in a single article all the ways in which you can move data into and out of Access. For example, here are just a few ways in which you might use the data-exchange features of Access:
  • To combine data that was created in other programs.
  • To transfer data between two other programs.
  • To accumulate and store data over the long term, occasionally exporting data to other programs such as Excel for analysis.

Overview of external data operations in Access

In many programs, you use the Save As command to save a document in another format, so that you can open it in another program. In Access, however, the Save As command is not used in the same way. You can save Access objects as other Access objects, and you can save Access databases as earlier versions of Access databases, but you cannot save an Access database as, say, a spreadsheet file. Likewise, you cannot save a spreadsheet file as an Access file (.accdb). Instead, you use the commands on the External Data tab in Access to import or export data between other file formats.
NOTE:  You can also write macros or Visual Basic for Applications (VBA) code to automate the import and export operations that are available on the External Data tab.

Types of data that Access can import, link to, or export

A quick way to learn about the data formats that Access can import or export is to open a database and then explore the External Data tab on the ribbon.
Access Ribbon Image
1. The Import & Link group displays icons for the data formats that Access can import from or link to.
2. The Export group displays icons for all the formats that Access can export data to.
3. In each group, you can click More to see more formats that Access can work with.
If you don’t see the exact program or data type that you need, chances are your data can be exported by the other program into a format that Access understands. For example, most programs can export columnar data as delimited text, which is then easily imported into Access.
The following table shows which formats can be imported into, linked to, or exported out of Access:

Program or format
Import allowed?
Linking allowed?
Exporting allowed?
Microsoft Office Excel
Yes
Yes
Yes
Microsoft Office Access
Yes
Yes
Yes
ODBC Databases (For example, SQL Server)
Yes
Yes
Yes
Text files (delimited or fixed-width)
Yes
Yes
Yes
XML Files
Yes
No
Yes
PDF or XPS files
No
No
Yes
E-mail (file attachments)
No
No
Yes
Microsoft Office Word
No, but you can save a Word file as a text file and then import the text file.
No, but you can save a Word file as a text file and then link to the text file.
Yes (you can export as Word Merge or as Rich Text)
SharePoint List
Yes
Yes
Yes
Data Services (see note)
No
Yes
No
HTML Documents
Yes
Yes
Yes
Outlook Folders
Yes
Yes
No, but you can export as a text file, and then import the text file into Outlook.
dBase files
Yes
Yes
Yes
NOTE:  To enable the Data Services button, Microsoft .NET 3.5 or later must be installed.

Import or link to data in another format

The general process for importing or linking data is as follows:
  1. Open the database that you want to import or link data into.
  2. On the External Data tab, click the type of data that you want to import or link to. For example, if your source data is in a Microsoft Excel workbook, click Excel.

    Access Ribbon Image 
  3. In most cases, Access starts the Get External Data wizard. In the wizard, you may be asked for some or all of the information in the following list:
    • Specify the source of the data (its location on disk).
    • Choose whether to import or link to the data.
    • If importing, choose whether to append the data to an existing table, or to create a new table.
    • Specify exactly which data in the document you want to import or link.
    • Indicate whether the first row contains column headings, or whether it should be treated as data.
    • Specify the data type of each column.
    • Choose whether to import the structure only, or the structure and the data together.
    • If importing, specify whether you want Access to add a new primary key to the new table, or use an existing key.
    • Specify a name for the new table. 
      NOTE:  It’s a good idea to look at your source data ahead of time so that you know the correct answers to these questions when the wizard asks for them.
  4. On the last page of the wizard, Access usually asks you if you want to save the details of the import or link operation. If you think you’ll need to perform the same operation on a recurring basis, select theSave import stepscheck box, fill in the information, and then click Close. Then, you can click Saved Imports on the External Data tab to re-run the operation.
After you have completed the wizard, Access notifies you of any problems that might have occurred during the import process. In some cases, Access might create a new table called ImportErrors, which contains any data that it was unable to import successfully. You can examine the data in this table to try to find out why the data did not import correctly.
For more information about importing or linking to data in a specific format, search the Access Help system for articles and videos that cover that format.

Export data to another format

The general process for exporting data from Access is as follows:
  1. Open the database that you want to export data from.
  2. In the Navigation Pane, select the object that you want to export the data from. You can export data from table, query, form, and report objects, although not all export options are available for all object types.
  3. On the External Data tab, click the type of data that you want to export to. For example, to export data in a format that can be opened by Microsoft Excel, click Excel.

    Access Ribbon image

    In most cases, Access starts the Export wizard. In the wizard, you may be asked for information such as the destination file name and format, whether to include formatting and layout, which records to export, and so on.
  4. On the last page of the wizard, Access usually asks you if you want to save the details of the export operation. If you think you will need to perform the same operation on a recurring basis, select the Save export stepscheck box, fill in the information, and then click Close. Then, you can click Saved Exports on the External Data tab to re-run the operation.
For more information about exporting to a specific format, search the Access Help system for articles and videos that cover that format.
Subscribe to: Posts (Atom)