Importing Information

Step 1 of the Import Text Wizard

Step 2 of the Import Text Wizard: Specify the text delimiter that separates the fields and the field names.

Step 4 of the Import Text Wizard: Specify the data type of the imported fields.

Step 5 of the Import Text Wizard: Specify a primary key.

People from different countries speak different languages, so naturally computer programs save files in different formats. Fortunately, just like some people can speak several languages, Access can read and write in other file formats. This lesson shows how to open one of the most common file formats in programs a tab-delimited text file in Microsoft Access.

1. Start Microsoft Access, if necessary, and open the Lesson 11 database.

   Ready? Here's how to import a text file.

2. Select File » Get External Data » Import from the menu.

   Another way to import an external data file is to right-click any empty area of the Database window and select Import from the shortcut menu.

   The appears. Normally, the Import dialog box only displays Microsoft Access databases. To import files created with other programs, you need to select the file type you want from the Files of type list in your case, text files.

3. Click the Files of type list arrow and select Text Files.

   Access displays any text files in the current folder. Next you have to specify the text file that you want to open.

4. Browse to your Practice folder and double-click the Europe Expenses file.

   The dialog box appears, as shown in figure. You must specify how the information is stored in the text file. There are two options:

   • Delimited: Tabs, colons, semicolons, or other characters separate items in your text file. This is the most common (and default) option.

   • Fixed Width: All the items in your text file are the same length.

   The Europe Expenses is a tab-delimited text file that is, tabs separate its fields so you don't need to make any changes and can continue on to the next step.

5. Click Next.

   The second step of the Import Text Wizard appears, as shown in figure 11-2. Here you have to specify the delimiter character used to separate the fields in the text file. Surprisingly, Access is usually smart enough to figure out which character is used as the delimiter (usually a tab or comma). What is vitally important here is the "First Row Contains Field Names" check box. Hopefully, the data you're importing will include field names in the first row; otherwise, you will have to add the field names to the table yourself later. Luckily, the Europe Expenses text file does include field names in the first row, so we'll need to tell this to Access.

6. Check the First Row Contains Field Names check box.

   Quotation marks or text qualifiers may surround some text entries and must be removed.

7. Click the Text Qualifier arrow, select " and click Next.

   Next Access asks where you want to import the data to a new table or to an existing table that you select from a drop-down list. We want to store the imported data in a new table, so we don't have to make any changes here.

8. Click Next.

   The next step of the Import Text Wizard allows you to specify the data types for the fields in the imported data, as shown in figure. For example, you could specify that a particular field is a date or number field. Access is usually quite smart at guessing the data type for most fields, but you might want to double-check each imported field just to be sure. Simply click the field heading that you want to view and make any changes to the data type.

9. Click Next.

   Next the Import Wizard asks if you want to add a primary key to the imported data, as shown in figure. You can let Access add an AutoNumber primary field to the new table, use one of the imported fields as the primary (providing that it meets the criteria for primary fields), or not use a primary key.

10. Select the No primary key option and click Finish. Click OK to confirm the creation of the new table.

    Access imports the text file and stores it in a new table called "Europe Expenses."

    Table below lists importable and .

Importable and Exportable File Formats and Extensions

File Format	Extensions
Microsoft Excel	.xls, .xlt
Lotus 1-2-3	.wk4, .wk3, .fm3, .fmt, .all, .wk1, .wks
Text (both tab- and comma-delimited)
Dbase 2, 3, 4	.dbf
Microsoft Access	.mdb

TO IMPORT INFORMATION FROM ANOTHER FILE INTO ACCESS:

1. IN THE DATABASE WINDOW, SELECT FILE » GET EXTERNAL DATA » IMPORT FROM THE MENU.

2. SELECT THE TYPE OF FILE YOU WANT TO IMPORT FROM THE FILES OF TYPE LIST.

3. BROWSE TO THE APPROPRIATE DRIVE AND/OR FOLDER AND DOUBLE-CLICK THE FILE YOU WANT TO IMPORT.

4. FOLLOW THE ON SCREEN INSTRUCTIONS OF THE IMPORT TEXT WIZARD TO IMPORT THE DATA.