Text files are the common denominator of documents. Almost every program that works with words and numbers can generate some kind of a text file, in addition to files in its native format. Access can import tabular data (tables and lists) from text files if the data has been stored in a recognizable format. The most common formats are called delimited and fixed width (which will be discussed later in this chapter).
In a delimited text file, each record ends in a carriage return, and each field is separated from the next by a comma or some other special character, called a delimiter. If a field contains one of these special characters, you must enclose the entire field in quotation marks. (Some people enclose all fields in quotation marks to avoid having to locate and enclose the special cases.)
In this exercise, you will import information about The Garden Company’s employees, which is stored in a comma-delimited text file, into the Employees table in the GardenCo database.
USE the GardenCo database and the Employees text file in the practice file folder for this topic. These practice files are located in the My DocumentsMicrosoft PressAccess 2003 SBSImportingImportDText folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.
OPEN the GardenCo database and acknowledge the safety warning, if necessary.
On the File menu, point to Get External Data, and then click Import.
In the Files of type list, click Text Files.
Tip
Text files typically have an extension of .txt. However, some programs save delimited text files with a .csv or .tab extension. You will also occasionally see text files with an extension of .asc (for ASCII). Fixed-width files are sometimes stored with an extension of .prn (for printer), but Access doesn’t recognize this extension, so you would have to rename it with one it does recognize. All acceptable extensions are treated the same way by Access.
Navigate to the My DocumentsMicrosoft PressAccess 2003 SBSImportingImportDText folder, click Employees, and then click Import.
Access displays the first page of the Import Text Wizard.
Each field is enclosed in quotation marks, and there is a comma between them. The selected file is delimited, so that option is selected.
Click the Advanced button to display the default import specifications for this file.
The Employees Import Specification dialog box appears, in which you can fine-tune the import process.
Click Cancel to close the Employees Import Specification dialog box, and then click Next.
The wizard breaks the file into fields, based on its assumption that items are separated by commas. From the neat columns you see here, this assumption is obviously a good one. If the columns were jumbled, you could choose a different delimiter from the options at the top of this page.
Select the First Row Contains Field Names check box, and then click Next.
The background of the first row is gray to indicate that these entries are field names.
Select the In an Existing Table option, click Employees in the drop-down list, and then click Next.
Click Finish to import the text file into the Employees table.
A message appears informing you that the file was imported.
Click OK to close the message box, and then double-click Employees to open the table and confirm that nine records were imported from the employees list.
Close the Employees table.
CLOSE the GardenCo database.