Import Data from Another Source

To Import data from a text file:

If your goal is to store some or all data that is in a text file in an Access database, you can 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.

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.

Note   When you open a text file in Access (click the Microsoft Office Button , click Open, and in the Open dialog box, change the Files of Type list box to All Files and then select 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.

1. Prepare the source file

a. Open the source file in a text editor, i.e., Microsoft Word.

Note   You can import only one file during an import operation. To import multiple files, repeat the import operation for each file.

b. Review the contents of the source file and take action as described in this table.

1. Element Description
   

   Delimited or fixed-width	Make sure the file consistently follows one of the formats. If the file is delimited, identify the delimiter. If the file has fixed-width fields, make sure each field is the same width in every record.
   Text qualifiers	Some delimited files might contain field values that are enclosed in single or double quotation marks, as shown here: “Pernille Halberg”,25,4/5/2006,”New York” “Daniel Brunner”,27,2000,”Chicago” 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: 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. 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.
   Extraneous characters	Review and remove extra characters, such as tabs, line feed, and carriage returns.
   Data types	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, later in this article.
   Field names	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.

c. Close the source file, if it is open. Keeping the source file open might result in data conversion errors during the import operation.

2. Start the import process in Access.

a. Open the Access database in which the imported data will be stored.

If you don’t want to store the data in any of your existing databases, create a blank database.

Click the Microsoft Office Button , and then click New.

b. Before you start the import operation, decide whether you want to store the data in a new or existing table.

• If you choose to store the data in a new table, Access creates a table and adds the imported data to it. If a table with the specified name already exists, Access overwrites the contents of the table with the imported data.
• Append to an existing table  If you are adding the data to an existing table, the import process appends the data to the specified table.As you proceed, remember that most append operations fail because the source data does not match the structure and field settings of the destination table. To avoid this, open the table in Design view, and review the following:
  • First row.  If the first row of the source text file does not contain field names, make sure the position and data type of each column matches those of the corresponding field in the table. For delimited text files, if the first row contains column headings, the order of columns and fields need not match, but the name and data type of each column must exactly match those of its corresponding field. 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.
  • Missing or extra fields.  If one or more fields are not in the destination table, add them before you start the import operation. However, if the destination table contains fields that don’t exist in the source file, you need not delete them from the table as long as they accept null values. Note:  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.
  • Primary key  If the table contains a primary key field, the source file must contain a column that contains values that are compatible with the primary key field. In addition, the imported key values must be unique. If an imported record contains a primary key value that already exists in the destination table, the import operation displays an error message. You must edit the source data so that it contains unique key values and then start the import operation 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.

c. On the External Data tab, in the Import group, click Text File.

Note   The Import commands are available only when a database is open.

d. In the Get External Data – Text File dialog box, in the File name box, type the name of the source file.

e. Specify how you want to store the imported data.

• To store the data in a new table, select Import the source data into a new table in the current database. You will be prompted to name this table later.
• To append the data to an existing table, select Append a copy of the records to the table and then select a table from the drop-down list.

f. Click OK.

g. Access scans the contents of the file and recommends how the file should be organized. If the file uses a delimiter to separate the fields, ensure that the Delimited option is selected. If the file has fixed-width fields, ensure that the Fixed Width option is selected.

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.

h. Click Next.

The information that the wizard displays depends on whether you select the Delimited option or the Fixed-Width option.

Delimited

Select or specify the character that delimits the field values. If the file users a text qualifier, in the Text Qualifier box, select either the double quotation mark (“) or the single quotation mark (‘). If the first row of the source file contains field names, select the First Row contains Field Names check box. Then, click Next.

Fixed-width

The wizard displays the contents of the file. If Access detects a columnar structure in the data, it places vertical lines in the data to separate the fields. Review the structure suggested by the wizard and, if necessary, follow the instructions on the wizard page to add, remove, or adjust the lines. Then, click Next.

i. If you chose to append the data, skip to step 12. If you are importing the data to a new table, click Next. At this point, you should review the field properties displayed in the wizard.

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.

j. Click a column in the lower half of the wizard page to display the corresponding field’s properties. Review and change, if you want, the name and data type of the destination field.

Access reviews the first 25 rows in each column to suggest the default data type for the corresponding field. If there are different types of values, such as text and numeric values, in the first 25 rows of a column, the wizard suggests a data type that is compatible with all or most of the values in the column — that is often the Text data type. Although you can choose a different data type, remember that values that are not compatible with the data type you choose are either ignored or converted incorrectly.

k. To create an index on the field, set Indexed to Yes. To altogether skip a source column, select the Do not import field (Skip) check box. Then click Next.

l. If the records are being added to a new table, the wizard prompts you to specify a primary key for the table. If you select Let Access add primary key, Access adds an AutoNumber field as the first field in the destination table, and automatically populates it with unique IDs, starting with 1. Click Next.

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.

m. Access displays the final page of the wizard. If you are importing records into a new table, specify a name for the destination table. In the Import to Table box, type a name for the table. If the table already exists, a prompt asks you whether you want to overwrite the existing contents of the table. Click Yes to continue or No to specify a different name for the destination table.

To Import data from a spreadsheet:

Follow all the steps above to: 2. Start the Import Process in Access.

Then:

On the External Data tab, in the Import group, click Excel.

1.  Note:   The External Data tab is not available unless a database is open.
2. In the Get External Data – Excel Spreadsheet dialog box, in the File name box, specify the name of the Excel file that contains the data that you want to import.-or-Click Browse and use the File Open dialog box to locate the file that you want to import.
3. Specify how you want to store the imported data.To store the data in a new table, select Import the source data into a new table in the current database. You will be prompted to name this table later.To append the data to an existing table, select Append a copy of the records to the table and select a table from the drop-down list. This option is not available if the database does not contain any tables.Note   To link to the data source by creating a linked table, see the section Link to data in Excel, later in this article.
4. Click OK.The Import Spreadsheet Wizard starts, and leads you through the import process. Go to the next set of steps.

Use the Import Spreadsheet wizard

1. On the first page of the wizard, select the worksheet that contains the data that you want to import, and then click Next.
2. On the second page of the wizard, click either Show Worksheets or Show Named Ranges, select either the worksheet or the named range that you want to import, and then click Next.
3. If the first row of the source worksheet or range contains the field names, select First Row Contains Column Headings and click Next.If you are importing the data into a new table, Access uses these column headings to name the fields in the table. You can change these names either during or after the import operation. If you are appending the data to an existing table, ensure that the column headings in the source worksheet exactly match the names of the fields in the destination table.If you are appending data to an existing table, skip directly to step 6. If you are adding the data to a new table, follow the remaining steps.
4. The wizard prompts you to review the field properties. Click a column in the lower half of the page to display the corresponding field’s properties. Optionally, do any of the following:
   • Review and change, if you want, the name and data type of the destination field.Access reviews the first eight rows in each column to suggest the data type for the corresponding field. If the column in the worksheet contains different types of values, such as text and numbers, in the first eight rows of a column, the wizard suggests a data type that is compatible with all of the values in the column — most often, the text data type. Although you can choose a different data type, remember that values that are not compatible with the data type you choose will be either ignored or converted incorrectly during the import process. For more information about how to correct missing or incorrect values, see the section Troubleshoot missing or incorrect values, later in this article.
   • To create an index on the field, set Indexed to Yes.
   • To completely skip a source column, select the Do not import field (Skip) check box.

   Click Next after you finish selecting any options.

5. In the next screen, specify a primary key for the table. If you select Let Access add primary key, Access adds an AutoNumber field as the first field in the destination table, and automatically populates it with unique ID values, starting with 1. Click Next.
6. In the final wizard screen, specify a name for the destination table. In the Import to Table box, type a name for the table. If the table already exists, Access displays a prompt that asks whether you want to overwrite the existing contents of the table. Click Yes to continue or No to specify a different name for the destination table., and then click Finish to import the data.If Access was able to import some or all of the data, the wizard displays a page that shows you the status of the import operation. In addition, you can save the details of the operation for future use as a specification. Conversely, if the operation completely failed, Access displays the error message An error occurred trying to import file.
7. Click Yes to save the details of the operation for future use. Saving the details helps you repeat the operation at a later time without having to step through the wizard each time.

Adapted from Microsoft Access Help Feature