Importing an ASCII (text) File into an Access File

You may have requested data from a hospital information system. It will probably arrive in a text file (extension.txt), which is essentially an ASCII file.

To be imported into a database, the ASCII file must be in one of two formats: a fixed format or ASCII delimited. To import either type into Access, have open an Access database into which you wish to import the file.

In Access 2003 or earlier versions. On the menu line click on File>Get External Data and answer all the questions on the screen.

In 2007, open the External Data Ribbon and make your selection from the Import Group.

You can create field names as you import, or later in the Design mode.

Fixed Format

A fixed format file has every character lined up as though it were in a separate cell of a table. The file will look like the following:

To import this file, one would tell the database that the first name field started in column 1, and was 6 characters long. The last name field starts at column 8 and is 5 characters long. Age starts at column 13 and is 3 characters long. It is not necessary to have a space between the fields, just to know where each field starts and how long it is. The ASCII file will not have any lines or numbers thus it may be necessary to count the number of characters in a field. If the person who gives you the file can furnish this information it will save you this task. There can be no hard returns between the characters in a record.

ASCII Delimited

An ASCII delimited file has all the fields separated by a given character. Often this character is a comma, in which case the file will be called a "comma delimited file." Sometimes this character is the tab key. These files will be called "tab delimited." Either of these may have an extension of ".csv." In these files text fields will be contained within quotation marks and numeric fields will be plain. The above file in a comma delimited file will look like the following:

"Sally","White",35
"Anne","King",43

Import an Excel File

Created November 20, 2011