Type Conversion Failure Occurs When Importing Microsoft Excel Data

Article translations Article translations
Article ID: 109376 - View products that this article applies to.
This article was previously published under Q109376
Novice: Requires knowledge of the user interface on single-user computers.

Expand all | Collapse all

On This Page

SYMPTOMS

When you import a Microsoft Excel spreadsheet to a new table or to an existing table in a Microsoft Access database, you may receive the following error message:
"Type Conversion Failure."

CAUSE

When you import a Microsoft Excel spreadsheet to a new or existing table, Microsoft Access checks the first row of the spreadsheet to determine the data types for the table. If the first row of the spreadsheet contains a numeric value in a column that allows alphabetical and numeric characters, Microsoft Access defines the field type in the table for that column as numeric. This is also true even if the field in the existing table is defined as text. It will not change the data type of the field, but it will cause errors during the import process. If there are alphabetical characters in other rows in this column in the spreadsheet, this error message occurs.

RESOLUTION

To work around this behavior, insert an extra row of data as the first row in the spreadsheet. Insert alphabetical characters in any columns that are defined as alphanumeric columns. Enter numeric characters in any columns that are defined as numeric columns. After you have imported the spreadsheet into Microsoft Access, delete the extra row of data from the spreadsheet and the table.

MORE INFORMATION

Steps to Reproduce the Behavior


  1. Open the sample database NORTHWIND.MDB (or NWIND.MDB in Microsoft Access 2.0 and 1.x).
  2. Export the Employees table as a Microsoft Excel spreadsheet that is named Employee.xls.
  3. Open the Employee.xls file in Microsoft Excel.
  4. In the first row of the Postal Code column, type 98122, and then press ENTER. Type this postal code even if the current value is already 98122.
  5. Close the spreadsheet and then save your changes.
  6. In Microsoft Access, import the spreadsheet as a new table. Select the First Row Contains Column Headings check box in the Import Spreadsheet Wizard dialog box (or the First Row Contains Field Names check box in the Import Spreadsheet Options dialog box in Microsoft Access 2.0 or 1.x).
  7. After the import is complete, a link_ImportErrors table appears in the Database window.
  8. Open the link_ImportErrors table. Note the
    "Type Conversion Failure"
    error for all postal codes that contain alphabetical characters.

Properties

Article ID: 109376 - Last Review: January 18, 2007 - Revision: 2.2
APPLIES TO
  • Microsoft Access 97 Standard Edition
  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Excel 95 Standard Edition
Keywords: 
kbinterop kbprb KB109376
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com