| Applies to |
Microsoft Office Access 2003
Microsoft Access 2000 and 2002 |
Note Unlike some other Microsoft Office programs, it is not possible to open text files or data files from different programs in Access and save their data directly as an Access database. It can take a little work to get your data into Access, but once you see what you can do with it —the power of queries, forms, and reports — you'll see it was worth the effort.
The easiest and quickest way to import data from a delimited text file (delimited text file: A file containing data where individual field values are separated by a character, such as a comma or a tab.) into Access is by using the Import Text Wizard. To import data, you will first need to have a database already open — either a database that already contains data (you can append data to an existing table), or an empty database (Access can create a new table for you).
If you want to append data to an existing table, your text file should not have column headings — they will interfere with the import process. To remove column headings from your text file, simply delete the first row. Confirm that the columns of data in your text file will work with the data types of the fields in your Access table. For example, to successfully import a date into a Date/Time field in an Access table, the field in the text file should be in a format that Access will recognize as a date. It may take a little experimentation on your part to get this right in your text file.
Before you import your text file, make sure:
- The file doesn't contain blank rows.
- The values have consistent data types among the rows. For example, a text file containing data separated by commas (CSV) might look like the following.
- PartName,PartNum,Cost,Location
- rocker arm,23356,28.50,MA
- bearing,5912,3.45,AL
- gasket,903321081,n/a,CT
- lifter,3778204,12.88,CA
spring,60429,,MA
In this example, the first row contains column headings and the next five rows contain data. There is a potential problem — in the third row of data (starting with "gasket), instead of a numeric value in the Cost column, the value is "n/a," which is text. This will cause this column of numbers to be imported as text — consequently, any math you try to perform on that field will fail, since the values aren't stored as numbers (remember that a number can be stored as a numeric value or as text).
The solution? Replace "n/a" with 0, or with an empty value (a pair of commas with no space separating them, as shown in the last row).
- There are no extra or missing columns. If there are fields that won't have values, they should be represented by an empty pair of commas (,,).
Example: Import records from a text file into an existing table
The following table shows the structure (four fields) and contents (two records) of a table in Access — with Integer, Text, and Date/Time data types.
| EmployeeID (Integer) |
LName (Text) |
FName (Text) |
DOB Date/Time) |
| 1001 |
Lang |
Eric |
4/05/1973 |
| 2189 |
Diaz |
Brenda |
11/25/1968 |
The following two lines show the text file containing two records, each with four columns (fields) of data separated by commas —the same data that is shown in the preceding Access table. Since we want to append these two records, there are no column headings in the text file. Also, note that the dates in the text file are in quite different formats — Access will recognize both formats while importing the data, and will successfully import both records.
1001,Lang,Eric,Apr 5 1973
2189,Diaz,Brenda,11/25/68
To start the Import Text Wizard
- On the Tools menu, point to Get External Data and then click Import.
Important The Get External Data command will not be available unless you already have an Access database open.
- In the Files of type box in the Import dialog box, click Text files (*.txt,*.csv,*.tab,*.asc), find your file, and click Import.
- Follow the steps in the Import Text Wizard, and, on the third page of the wizard ("Where would you like to store your data?"), choose an existing table.
- Finish the rest of the steps in the wizard.
Example: Import records from a text file into a new table
The following three lines show the text file containing column names and two records, each with four columns (fields) of data separated by commas. Note that the dates in the two records are in quite different formats — Access will recognize both formats while importing the data, and will successfully import both records.
EmployeeID,LName,FName,DOB
1001,Lang,Eric,Apr 5 1973
2189,Diaz,Brenda,11/25/68
To start the Import Text Wizard
- On the Tools menu, point to Get External Data and then click Import.
Important The Get External Data command will not be available unless you already have an Access database open.
- In the Files of type box in the Import dialog box, click Text files (*.txt,*.csv,*.tab,*.asc), find your file, and click Import.
- Follow the steps in the Import Text Wizard, and, on the third page of the wizard ("Where would you like to store your data?"), choose an existing table.
- Finish the rest of the steps in the wizard.
More information
- The procedure for creating an Access database by opening a text file is similar to the procedure for using a spreadsheet. See the article Creating a new Access database from an Excel spreadsheet. You can find this article in the See Also list.
- Because the format of the file you are linking determines which wizard appears, the Link Text Wizard guides you through the process of creating and linking a database from a text file.
- You can't update data in a linked text file because Access requires an index in order to update the data. If you need to update your data, you may want to consider importing the text file into Access.
