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. 1. Save then close the .txt file. 2. Next, go to "New" on the "Tables" tab. 3. Click on "Import table." 4. Change the "files of type" to Text Files. 5. Navigate to the text (.txt) file that you would like to import. 6. 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).***

Select "Delimited." 7. Click "Next." 8. Select the character that you are using to separate the columns. 9. 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, click "Advanced…" 10. 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. 11. 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" previously selected. 12. For the "Language" and "Code Page" options, use the defaulted values. 13. Select the format of the dates and times contained in your data. 14. For the "Field Information": i. type the column heading names, in the "Field Name" fields. This would be the place to change the column name headings if so desired; ii. select the data type for each of your columns from the drop-down boxes; iii. select whether or not you would like to index each individual column (yes, no duplicates or yes, duplicates ok, or no); iv. if you do not want to import certain columns, check the "Skip" box on that column’s row. 15. Click "Save As…" 16. Give the specification a name or use the default name provided. 17. Click "Ok" to save the specification. 18. Click "Ok" to return to the "Import Text Wizard." 19. Click "Next" twice. 20. 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 box. 21. 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. 22. Click "Next." 23. Select whether or not you want Access to add a primary key, choose your own primary key or no primary key. 24. Click "Next." 25. Click "Finish." 26. If all of your records could not be imported successfully, you will receive an error message and an "[table name]_ImportErrors" table.
Creating the import macro in Microsoft Access
The specification that we created in the previous section will be used in this section. 1. Go to "Macros" then click "New" 2. On the first line, click the drop-down box 3. Go to "Transfer Text" 4. 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." 5. In the "Specification name" field, select the specification created in the previous section. 6. In the "Table Name" field, type the name you would like to assign to the table. 7. In the "File Name" field, type the path to the text (.txt) file. For example, c:/desktop/[filename].txt. 8. 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. 9. 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.