Importing Text Files into Access

To import text into Access, you might work with one of the two previously mentioned text files: delimited or fixed-width. As you learn more about other software applications, you'll find that many can export data to a text file. Let's demonstrate the import process by importing the two text files you created in the last section back into Access. Follow these steps:

  1. In Access, select Get External Data from the File menu; then select Import from the resulting submenu.

  2. In the Import dialog box, select Text Files (*.txt;*.csv;*.tab;*.asc) from the Files of Type control.

  3. Select Catalogs.txt and click Import . You could use the Look In control to find files in another folder, but Catalogs.txt is in the default folder.

  4. The wizard tries to interpret the incoming data and select the appropriate file type: delimited or fixed-width. It won't always make the right choice, but this time, it does. The file is a delimited text file, so click Next .

  5. In the next pane, check the First Row Contains Field Names option so the wizard will know what to do with the field names in the first row. (You might remember that you included the field names when you exported the data earlier.) When you do, the wizard removes the field names from the record display. The wizard also correctly identifies the delimiter , which in this case is a comma. Click Next .

  6. You can import the data into an existing table or a new table. The default is to create a new table, and that's what you want to do, so click Next .

  7. You can rename the fields, apply an index, or specify a data type in the next pane. Don't make any changes; just click Next .

  8. Select the Choose My Own Primary Key option in the next window. The wizard defaults to the name field, which is what you want, so after selecting the primary key option, click Next .

  9. In the final pane, change the default name to CatalogsDelimited , click Finish , and then click OK to clear the confirmation message. You already have a table named Catalogs, which is why you need to change the name.

  10. In the Database window, click the Tables shortcut and open CatalogsDelimited.

The new table looks just like the original, doesn't it (refer to Figure 16.3)? Exporting the data to a text file and then importing it back into Access hasn't changed anythingthe data is no worse for the wear. Notice that Access used the names in that first row as field names for the new table.

To import the fixed-width text file, follow these steps:

  1. Repeats steps 1 and 2 from the previous exercise.

  2. In the Import dialog box, select CatalogsFixedWidth.txt and click Import .

  3. The wizard correctly identifies the text file as a fixed-width file, so click Next .

  4. There's an incorrect break in the first field, so double-click that first break line to delete it. Another one occurs at 87 in the middle of the Country field. Delete it as well. Then click Next .

  5. You're importing the data into a new table, so retain the default option of In a New Table ; then click Next .

  6. Take a minute to review the wizard's final choices in the next pane. You can change a field's name, change its data type, or apply an index. You don't need to modify anything at this point, though, so click Next when you're ready to continue.

  7. In the next pane, select the Choose My Own Primary Key option, select the name field (if the wizard doesn't), and then click Next .

  8. In the last pane, click Finish and then click OK to clear the confirmation message.

  9. Click the Tables shortcut in the Database window; then open the CatalogsFixedWidth table. It looks just like the original table (refer to Figure 16.3).

Importing a text file can require several settings, but the actual process is simple and the wizard usually does a good job of handling the data.

Absolute Beginner's Guide to MicrosoftR Access 2002
Absolute Beginner's Guide to MicrosoftR Access 2002
Year: 2002
Pages: 133 © 2008-2017.
If you may any questions please contact us: