Creating an Access database from a text file - Access

Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.

Email this link

Printer-Friendly Version Bookmark and Share

Creating an Access database from a text file

Applies to: Microsoft Office Access 2003

Applies to
Microsoft Office Access 2003 Microsoft Access 2000 and 2002

The easiest and quickest way to import data 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 a table), or a new database (Access can create a new table for you).

Note 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. 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.

Show Example: Import records from a text file into an existing table in Access

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)	FName (Text)	LName (Text)	DOB (Date/Time)
1001	Eric	Lang	4/05/1973
2189	Brenda	Diaz	11/25/1968

The following two lines show a text file containing two records, each with four columns (fields) of data separated by commas. 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.

6249,Willis,Johnson,Jan 25 1955
2134,Sandeep,Katyal,7/14/74

To start the Import Text Wizard

On the Tools menu, point to Get External Data and then click Import.
Note In order for the Get External Data command to be available (that is, not dimmed), you must 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 an 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.