Import data from a text file

Importing data from a text file into a desktop database or an Access app is a two-step process- you'll first prepare the text file for the import and then import the data into the database or app. 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.

1. Prepare source file for import

   1. Open the source file in a text editor, like Notepad.

   2. Cleaning up the source data before importing it can give you cleaner import results. If your text file has elements described in the following table, take the suggested action:

Check your text file for these element	Do this
Delimited or fixed-width	To use a text file as a source file for importing, the contents of the file must be organized in such a way that the importing wizard can divide the contents into records (rows) and fields (columns). Text files that are organized properly fall into one of two types delimited files and fixed-width fields: Tip:  If you are not sure whether your file has fixed-width or delimited fields, see types of text file formats. Delimited file: Identify the delimiter. If the first row has column headings, the order of columns need not match, but the name and data types of the text file must exactly match the corresponding field data type. Fixed-width fields file: Make sure each field is the same width in every record.
Text qualifiers	Some delimited files might have field values enclosed within quotation marks like this: "Pernille Halberg",25,4/5/2006,"New York" "Daniel Brunner",27,2000,"Chicago" The quotation marks are called a text qualifier and are not required, unless one the following conditions is true: The field delimiter appears as part of the field values. For example, if comma is used as the field delimiter, and New York, Chicago is a valid field value, you must enclose the value within a pair of qualifiers, like this: "New York, Chicago" You want Access to treat non-text values, such as 0452934 and 0034539 as text values and store them in a Text field. When importing, you can specify whether the file uses a qualifier and, specify the qualifier character. Make sure you use the same text qualifier throughout the file and enclose only text values within the quotation marks.
How many fields (columns) can be imported?	You can import upto 255 fields (columns) into a table.
Skipping records and values	To import only a portion of the text file, edit the source file before you import because you cannot skip records (rows) during the import. You can skip fields (columns) only when you add data to a new table. When you add data to an existing table, 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 text file. If you are adding the records to an existing table and you have blank fields, 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.
Extraneous characters	Review and remove extra characters, such as tabs, line feed, and carriage returns.
Data types	Access scans the first 25 rows of a source file to determine the data type of the fields in the table so, make sure that every column in the first 25 rows of the source has the same data type. Also make sure any non-text values to be treated as text values are enclosed in quotation marks. Note:  Any mixed values in the rows following the 25th row of the source might be skipped or converted incorrectly during the import. See how to fix this in the Troubleshoot missing or incorrect values section.
Field names	For delimited text files, if the file does not include the names of the fields, place them in the first row. You can specify that the values in the first row should be treated as field names when you import the data. When you import fixed-width text files, you cannot use the first row as the field name. Note:  When you add a delimited text file to an existing table, make sure that the name of each column exactly matches the name of the corresponding field in the table or the import would fail. To find the names of the fields in the existing table, open the table in Design view.

1. Close the source file to avoid data conversion errors during the import operation.

• Import a text file

This process describes importing into a desktop database. For Access apps, see importing data into an Access app.

Note:  You can import only one file at a time. For multiple files, repeat the import steps.

1. If you are importing into an existing database, open it. Or create a blank database or app and import the data into a new table. When naming a new table, make sure that another table with the same name doesn't already exist.

   To avoid common import process failures, open the destination table in Design view and review the following:

   Check table	Action
   First row	If the first row of the source text file does not have field (column) names, make sure the position and data type of each column matches the corresponding field in the destination table.
   Missing or extra fields	If any field is missing in the destination table, add them before importing. If the destination table has fields that don't exist in the source file, you don't need to delete them from the table as long as the fields accept null values. Tip:  A field will accept null values when its Required field property is set to No and its ValidationRule property setting doesn't prevent null values.
   Primary key	If the table you are importing into has a primary key field, the source file must have a column that contains values that are compatible with the primary key field. Also, the imported key values must be unique. If an imported record has a primary key value that already exists in the destination table, the import would show an error message. If this happens, add unique key values to the source data and try the import again.
   Indexed fields	If the Indexed property of a field in the table is set to Yes (No Duplicates), the corresponding column in the source text file must contain unique values.

2. Click External Data >Text File and complete the steps in the wizard.

Troubleshoot missing or incorrect values

If you see an error message at the end of the import process it is usually means the import operation failed. If you are prompted to save the details of the operation, it either means that some or all of the data was imported. To verify, 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 has information about a specific error, and the information in the Error field should help you troubleshoot.

Tip:  If you find just a few missing values, you can add them directly to the table in Datasheet view. But if you've got a lot of missing data or corrections, you'll save time by correcting the problem in the source file and repeating the import process again.

Use the information in the table below to correct missing or incorrect values:

Issue	Resolution
-1 or 0 values	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.
Multivalued fields	You cannot import multiple values in a field. The list of values are treated as a single value and placed in a text field, separated by semicolons.
Truncated data	If data appears truncated in a column, try increasing the width of the column in Datasheet view. If that doesn't help, see if you have a numeric field data type set to Long Integer and change it to Double.
Data missing in primary key or indexed fields	Eliminate the duplicate values in the source file and try importing again. Duplicate values 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).
Null values	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: If the first 25 source rows have values of different data types, open the source file and rearrange the lines to make sure the first 25 rows do not contain mixed values in any of the fields. Then, try importing again. Enclose all non-text values that you want to store as text values in single or double quotation marks. During the import operation, select the appropriate data type for each field. If the data type is incorrect, you might see null values or incorrect values in the entire column.

Types of text file formats

Delimited files   : In a delimited file, each record appears on a separate line and the fields are separated by a single character, called the delimiter. The delimiter can be any character that does not appear in the field values, such as a tab, semicolon, comma, space, and so on. The following is an example of comma-delimited text.

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.

Fixed-width files:    Each record appears on a separate line and the width of each field remains consistent across records. For example, the first field of every record is always seven characters long, the second field of every record is always 12 characters long, and so on. If the actual length of a field's value varies from record to record, the values that fall short of the required width must be padded with trailing space characters. The following is an example of fixed-width text.

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.