How to Create a Microsoft Access Macro to Import Information from
a Text (.txt) File:
Macros are operations used in Microsoft Access in order to automate
functions in databases. They can be used to open reports, run queries,
update the databases and import from various file formats such as
Excel (.xls), database, (.dbf), and text (.txt).
This article will explain, in a step-by-step format, how to import
information from a text file, format the information for Access, and
create the macro so each step will not need to be created every day.
This will save you time and put some of the database operations into
the hands of the end-users and freeing you to do other things. Prior
to creating the macro, the creation and saving of the import specification
will be explained.
Importing from a text document (.txt)
Prior to importing a text file, eliminate any spaces in front of
the column headings. Usually, text file columns are separated by using
semicolons. Ensure that there are no spaces between the semicolon
and the column heading. By eliminating the spaces, you prevent importing
errors and column heading issues so Access can recognize the headings.
- Save then close the .txt file.
- Next, go to "New" on the "Tables" tab.
- Click on "Import table."
- Change the "files of type" to Text Files.
- Navigate to the text (.txt) file that you would like to import.
- The "Import Text Wizard" dialog box will open. Select
if your data is separated by characters such as commas, semicolons,
etc. (Delimited) or if your data is aligned in columns with spaces
between each field (Fixed width). If your information is not separated
using semicolons, commas, etc., then select "Fixed width".
When "Fixed width" is selected you will receive a dialog
box that will allow you to create column breaks in your data.
***It is recommended that your text columns be separated by semicolons
or commas (delimited).***
- Click "Next."
- Select the character that you are using to separate the columns.
- If the first row of your text (.txt) file data contains the column
headings, check the "First Row Contains Field Names" box.
If the first row of your data does not contain column headings,
- The "[Text filename] Import Specifications" dialog box
will open. In this box, you can select the file format, language,
date, time, and number formats and specify field information.
- For "File Format", if you used the delimited or fixed
width option in the previous step it will already be selected along
with the "Field delimiter" and "Text qualifier"
- For the "Language" and "Code Page" options,
use the defaulted values.
- Select the format of the dates and times contained in your data.
- For the "Field Information":
- type the column heading names, in the "Field Name"
fields. This would be the place to change the column name headings
if so desired;
- select the data type for each of your columns from the drop-down
- select whether or not you would like to index each individual
column (yes, no duplicates or yes, duplicates ok, or no);
- if you do not want to import certain columns, check the "Skip"
box on that column’s row.
- Click "Save As…"
- Give the specification a name or use the default name provided.
- Click "Ok" to save the specification.
- Click "Ok" to return to the "Import Text Wizard."
- Click "Next" twice.
- The resulting dialog box will ask you where you would like to
store your data. Select "In a new table" or "In an
existing table" along with the table name using the drop-down
- The remaining boxes will simply show what you selected in steps
6-11 as the formatting and column names for the fields. You can
change your settings at this point; however, you will need to resave
the specification with the new settings.
- Click "Next."
- Select whether or not you want Access to add a primary key, choose
your own primary key or no primary key.
- Click "Next."
- Click "Finish."
- If all of your records could not be imported successfully, you
will receive an error message and an "[table name]_ImportErrors"
Creating the import macro in Microsoft Access
The specification that we created in the previous section will be
used in this section.
- Go to "Macros" then click "New"
- On the first line, click the drop-down box
- Go to "Transfer Text"
- In the "Action Arguments" pane for:
1) "Transfer type" select "import delimited".
If you selected "Fixed width" as the file format, you
would select "import fixed width."
- In the "Specification name" field, select the specification
created in the previous section.
- In the "Table Name" field, type the name you would like
to assign to the table.
- In the "File Name" field, type the path to the text
(.txt) file. For example, c:/desktop/[filename].txt.
- In the "Has Field Names" field, select "yes"
if your text (.txt) file contains the field names; select "no"
if your text (.txt) file does not contain field names.
- You can leave the "HTML Table Name" and the "Code
Page" fields blank.
For end-user databases, you can attach the macro to a button. This
will allow the end-users to update the database at their discretion.