Microsoft Access and Text Files


Importing a Text File

You can create a table using data from a text file. If you intend to import a text document, format it so that Microsoft Access can recognize where a field starts and where it ends. The file can be created using Notepad. If the file is from another type of application, you can first convert its data to text, save it as a text file, and then import it.

If you are creating the (text) file in Notepad, the delimitation of a field is usually done by pressing Tab after creating the fields' content. Instead of the Tab key, you can also use a comma or a semi-colon to separate two fields. Since a field is usually made of more than one word (such as an address), you can enclose the content of each field in double quotes, as in "1600 Pennsylvania Avenue". A file that contains the data to be imported must also indicate where a record starts and where it ends. If you are creating the file in a text editor, the delimitation of a record is done by pressing Enter at the end of each record.

To import a text file in Microsoft Access:

  • On the Ribbon, click External Data and, in the Import section, click the Text File button
  • If you had previously created a table that would receive the values, in the Navigation Pane, right-click that table, position the mouse on Import, and click Text File

Exporting a Text File

The easiest way to use data from a Microsoft Access database to an external application is to save it as plain text. The reason is that almost every application that deals with databases can import text and convert it into a spreadsheet or a database table. This is only possible if the text file is appropriately formatted. Fortunately, if you ask Microsoft Access to save a table to text, it would take care of formatting it.

To save a table as text:

  • In the Navigation Pane, click the table you want to export. On the Ribbon, click External Data and, in the Export section, click the Text File button
  • In the Navigation Pane, right-click the table that holds the data, position the mouse on Export, and click Text File

This would open the Export - Text File dialog box with the name of the file using the .txt extension. The default folder where the file would be saved is My Documents. If you want another, you can select it by clicking the Browse button. Once you are ready to export, you can click OK. This would open the Export Text Wizard that you can follow:

You would have various options to specify how you want Microsoft Access to format the document. You would also choose whether to include the column headers or not.


Home Copyright © 2008-2016, FunctionX, Inc.