| Text files are the common denominator of all document types. Almost every program that works with words and numbers can generate some kind of a text file. Access can import tabular data (tables and lists) from delimited and fixed-width text files. 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 a fixed-width text file, the same field in every record contains the same number of characters. If the actual data doesn't fill a field, the field is padded with spaces so that the starting point of the data in the next field is the same number of characters from the beginning of every record. For example, if the first field contains 12 characters, the second field always starts 13 characters from the beginning of the record, even if the actual data in the first field is only 4 characters. Fixed-width text files used to be difficult to import into databases because you had to carefully count the number of characters in each field and then specify the field sizes in the database or in the import program. If the length of any field was even one character off, all records from that point on would be jumbled. That is no longer a problem with Access because the Import Text wizard makes importing a fixed-width text file simple. The only way to get the data of many older programs into Access is to export the data to a fixed-width text file and then import that file into Access. In this exercise, you will import information from a comma-delimited text file into an existing table in an Access database. USE the 05_ImportText database and the 05_Employees text file. These practice files are located in the Chapter03 subfolder under SBS_Access2007. OPEN the 05_ImportText database.
1. | On the External Data tab, in the Import group, click the Text File button.
 Tip Text files typically have a .txt extension. However, some programs save delimited text files with a .csv or .tab extension. You will also occasionally see text files with an .asc (for ASCII) extension. Fixed-width text files are sometimes stored with a .prn (for printer) extension, but Access doesn't recognize this extension, so you would have to rename it with one it does recognize. Access treats files with all acceptable extensions the same way. The Get External Data wizard starts.
| 2. | On the Select the source and destination of the data page, click Browse.
| 3. | In the File Open dialog box, navigate to your Documents\MSP\SBS_Access2007\Chapter03 folder, click the 05_Employees text file, and then click Open.
| 4. | Select the Append a copy of the records to the table option, and in the list, click Employees. Then click OK.
The Import Text wizard starts, displaying the content of the selected delimited text file.
 Each information field is enclosed in quotation marks, and the fields are separated by commas.
| 5. | Click the Advanced button.
The 05_Employees Import Specification dialog box opens. You can make changes to the default settings.
Tip If you want to import several files that deviate in some way from the default settings, you can specify the new settings and save them. Then as you open each of the other files, you can display this dialog box and click the Specs button to select and load the saved specifications. | 6. | In the Import Specification dialog box, click Cancel. Then in the Import Text wizard, click Next.
The wizard separates 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.
 | 7. | Select the First Row Contains Field Names check box, and click Next. Then click Finish.
Access imports the text file into the Employees table.
| 8. | On the Save Import Steps page, click Close.
| 9. | Open the Employees table to confirm that Access imported seven records from the text file.
|
CLOSE the 05_ImportText database.
Importing Information from Other Sources Access 2007 groups the less-common types of files you can import on the More list in the Import group on the External Data tab. These additional file types include: The process of importing data from any of these file types follows the same general pattern. The Get External Data wizard guides you through the process. In the next three exercises, you will experiment with this by importing data from several of these file types. |
|
