You can bring data from a text file into Access in two ways. If you want a copy of the data that you can edit within Access, import the file into a new or existing table by using the Import Text Wizard. If you simply want to view the latest source data within Access for richer querying and reporting, create a link to the text file in your database by using the Link Text Wizard.
This article explains how to import and link to a text file by using these wizards.
A text file contains unformatted readable characters, such as letters and numbers, and special characters such as tabs, line feeds and carriage returns. Access supports the following file name extensions — .txt, .csv, .asc, and .tab.
To use a text file as a source file for importing or linking, the contents of the file must be organized in such a way that the importing and linking wizards can divide the contents into a set of records (rows) and each record into a collection of fields (columns). Text files that are organized properly fall into one of two types:
1,Company A,Anna,Bedecs,Owner 2,Company C,Thomas,Axen,Purchasing Rep 3,Company D,Christina,Lee,Purchasing Mgr. 4,Company E,Martin,O’Donnell,Owner 5,Company F,Francisco,Pérez-Olaeta,Purchasing Mgr. 6,Company G,Ming-Yang,Xie,Owner 7,Company H,Elizabeth,Andersen,Purchasing Rep 8,Company I,Sven,Mortensen,Purchasing Mgr. 9,Company J,Roland,Wacker,Purchasing Mgr. 10,Company K,Peter,Krschne,Purchasing Mgr. 11,Company L,John,Edwards,Purchasing Mgr. 12,Company M,Andre,Ludo,Purchasing Rep 13,Company N,Carlos,Grilo,Purchasing Rep
1 Company A Anna Bedecs Owner 2 Company C Thomas Axen Purchasing Rep 3 Company D Christina Lee Purchasing Mgr. 4 Company E Martin O’Donnell Owner 5 Company F Francisco Pérez-Olaeta Purchasing Mgr. 6 Company G Ming-Yang Xie Owner 7 Company H Elizabeth Andersen Purchasing Rep 8 Company I Sven Mortensen Purchasing Mgr. 9 Company J Roland Wacker Purchasing Mgr. 10 Company K Peter Krschne Purchasing Mgr. 11 Company L John Edwards Purchasing Mgr. 12 Company M Andre Ludo Purchasing Rep 13 Company N Carlos Grilo Purchasing Rep
If your goal is to store some or all data that is in a text file in an Access database, you should import the contents of the file into a new table or append the data to an existing table. You can view and edit the imported data, and the changes you make to the data do not affect the source text file.
During the import operation, you can specify how the source file is organized, and whether you want to create a new table or append the data to an existing table.
Tip: If you aren't familiar with tables or how a database is structured, see the articles Create tables in a database or Database design basics.
At the end of the import operation, you can choose to save the details of the import operation as a specification. An import specification helps you repeat the operation at a later time without you having to step through the Import Text Wizard each time.
Typically, you import text data into Access for these reasons:
Note: When you open a text file in Access (by changing the Files of Type list box to All Files in the Open dialog box and then selecting your text file), Access starts the Link Text Wizard, which allows you to create a link to the text file instead of importing its contents. Linking to a file is different from importing its contents. For more information about linking to text files, see the section Link to a text file, later in this article.
Note: You can import only one text file during an import operation. To import multiple files, repeat the import operation for each file.
The character that encloses a field value is called a text qualifier. Text qualifiers are not required, but they are essential if either of the following is true:
During the import operation, you can specify whether the file uses a qualifier and, if so, specify the character that acts as the qualifier.
Make sure that the same text qualifier is used throughout the file and that only text values are enclosed within a pair of qualifiers.
Number of fields
The number of source fields must not exceed 255 — Access cannot support more than 255 fields in a table.
Skipping records and values
If you are interested in only a portion of the text file, edit the source file before you start the import operation. You cannot skip records during the import operation.
If you are adding the data to a new table, you can skip certain fields, but this option is not available if you are appending the contents to an existing table. When you append data to an existing table, the structure of the source data must match the structure of the destination table. In other words, the source data must have the same number of columns as the destination table, and the data types of the source data must match the data types of the destination table.
Blank lines and fields
Delete all unnecessary blank lines in the file. If there are blank fields, try to add the missing data. If you are planning to append the records to an existing table, make sure the corresponding field in the table accepts null values. A field will accept null values if its Required field property is set to No and its ValidationRule property setting doesn't prevent null values.
Review and remove extra characters, such as tabs, line feed, and carriage returns.
To avoid errors during importing, make sure each source field contains the same type of data in every line. Access scans the first 25 rows of a file to determine the data type of the fields in the table. We highly recommend that you make sure that the first 25 source rows do not mix values of different data types in any of the fields. Also make sure that non-text values that are to be treated as text values are enclosed in single or double quotation marks.
If the source file contains mixed values in the rows following the 25th row, the import operation might still skip them or convert them incorrectly. For troubleshooting information, see the section Troubleshoot missing or incorrect values in an imported table, later in this article.
For delimited text files, if the file does not include the names of the fields, it is good practice to place them in the first row. During the import operation, you can specify that Access treat the values in the first row as field names. When you import fixed-width text files, Access does not give you the option of using the values in the first row as the field name.
Note: When you append a delimited text file to an existing table, ensure that the name of each column exactly matches the name of the corresponding field. If the name of a column is different from the name of the corresponding field in the table, the import operation fails. To find the names of the fields, open the destination table in Design view.
Tip: A field will accept null values if its Required field property is set to No and its ValidationRule property setting doesn't prevent null values.
Note: To link to the data source by creating a linked table, see the section Link to a text file, later in this article.
Note: If the source text file contains tabs or other special characters, these are represented in the Import Text Wizard as small boxes between the columns of data.
Note: The wizard page on which you can specify information about fields you are importing is not displayed if you are appending records to an existing table.
Note: The wizard page on which you can specify information about fields you are importing is not displayed if you are appending records to an existing table.
If you see the message An error occurred trying to import file, the import operation completely failed. Conversely, if the import operation displays the page that prompts you to save the details of the operation, the operation managed to import all or some of the data. The status message also mentions the name of the error log table that contains the description of any errors that occurred during the import operation.
It is important to note that even if the status message indicates a successful operation, you should review the contents and structure of the table to ensure that everything looks correct before you start using the table.
Open the destination table in Datasheet view to see if all the data was successfully added to the table. You should then open the table in Design view to review the data type and other property settings of the fields.
The following table describes the steps you can take to correct missing or incorrect values.
Tip: While troubleshooting, if you find just a few missing values, you can add them directly to the table in Datasheet view. On the other hand, if you find entire columns or large number of values either missing or not imported properly, correct the problem in the source file. When you think you have corrected all known problems, repeat the import operation.
If the source file includes a field that contains only True or False values or only Yes or No values, and you selected Yes/No as the data type for the field, you will see -1 and 0 in the table. Open the table in Design view, and set the Format property to either True/False or Yes/No.
Access does not support importing multiple values in a field. The list of values are treated as a single value and placed in a text field, separated by semicolons.
If data appears truncated in a column, try increasing the width of the column in Datasheet view. If increasing the width doesn't resolve the issue, the cause might be that the data type of a numeric field is set to Long Integer when it should have been set to Double.
Data missing in primary key or indexed fields
Records that you are importing contain duplicate values that cannot be stored in the primary key field of the destination table or in a field that has the Indexed property set to Yes (No Duplicates) in an Access database. Eliminate the duplicate values in the source file and try importing again.
When you open the table in Datasheet view, you might find that some fields are blank. Do the following to minimize or eliminate any instances of null values in the table:
In addition, you might want to review the error log table from the last page of the wizard in Datasheet view. The table has three fields — Error, Field, and Row. Each row contains information about a specific error, and the contents of the Error field should help you troubleshoot the problem.
Complete list of error strings and troubleshooting hints
Description
A value in the file is too large for the FieldSize property setting for this field.
Type Conversion Failure
A value in the text file or worksheet is the wrong data type for this field. The value might be missing or might appear incorrect in the destination field. See the entries in the preceding table for more information about how to troubleshoot this issue.
This record's primary key value is a duplicate — that is, it already exists in the table.
Validation Rule Failure
A value breaks the rule set by using the ValidationRule property for this field or for the table.
Null in Required Field
A null value isn't allowed in this field because the Required property for the field is set to Yes.
Null value in AutoNumber field
The data that you are importing contains a null value that you attempted to append to an AutoNumber field.
A text value contains the text delimiter character (usually double quotation marks). Whenever a value contains the delimiter character, the character must be repeated twice in the text file; for example:
10 - 3 1/2"" disks/box
You use linking to connect to data in another file without importing it — by doing so, you can view the latest data in both the original program and in the Access file without creating and maintaining a copy in Access. If you don't want to copy the contents of the text file into your Access database, but still want to run queries and generate reports based on that data, you should link to, rather than import, the text file.
When you link to a text file, Access creates a new table that is linked to the source file. Any changes you make to the source file are reflected in the linked table, however, you will not be able to edit the contents of the corresponding table in Access. If you want to make changes to the contents or structure of the data, you should open the source file and make the changes in it.
During the linking operation, you can specify whether the file uses a qualifier and, if so, you specify the character that acts as the qualifier.
Ensure that the same text qualifier is used throughout the file and that only text values are enclosed within a pair of qualifiers.
Number of fields
The number of source fields must not exceed 255 — Access cannot support more than 255 fields in a table.
Skipping records and fields
You can skip certain fields, but you cannot skip records.
Blank lines and fields
Delete all unnecessary blank lines in the file. If there are blank fields, try to add the missing data in the source file.
Review and remove extra characters, such as tabs, line feeds, and carriage returns.
To avoid errors during linking, ensure that each source field contains the same type of data in every line. Access scans the first 25 rows of a file to determine the data type of the fields in the table. We highly recommend that you ensure that the first 25 source rows do not mix values of different data types in any of the fields. You should also ensure that any non-text values that you want to be treated as text values are enclosed in single or double quotation marks.
If the source file contains mixed values in the rows following the 25th row, the import operation might display error values or convert them incorrectly. For troubleshooting information, see the section Troubleshoot #Num! and incorrect values in a linked table, later in this article.
For delimited text files, if the file does not include the names of the fields, it is a good practice to place them in the first row. During the linking operation, you can specify that Access treat the values in the first row as field names. However, when you import fixed-width text files, there is no option for treating the values in the first row as field names.
Even if you see the message Finished linking table, you should still open the table in Datasheet view to verify that all the rows and columns show the correct data.
If you see errors or incorrect data anywhere in the table, take corrective action as described in the following table and try linking again. Remember that you will not be able to add the values directly to the linked table, because the table is read-only.
If the source file includes a field that contains only True or False values or only Yes or No values, and you selected Yes/No as the data type for the field, you will see -1 or 0 in the table. Open the table in Design view and set the Format property to True/False or Yes/No.
When you link data, Access does not enable support for multiple values in a field. The list of values is treated as a single value and placed in a text field, separated by semicolons.
If data appears truncated in a column, try increasing the width of the column in Datasheet view. If increasing the width doesn't resolve the issue, the cause might be that the data type of a numeric field is set to Long Integer, when it should have been set to Double.
When you open the table in Datasheet view, you might see that some fields contain #Num! instead of the actual value. Do the following to minimize or eliminate any instances of null values in the source file:
The following table lists cases where you will still see the #Num! error in fields:
If the values that are missing are of type.
And the destination field type is.
Numeric or Date
Replace all text values with values that match the data type of the destination field and then try linking again.
Replace all date values with numeric values and then try linking again.
Replace all numeric values with date values and then try linking again.
