ACC: Type Conversion Failure When Importing MS Excel Data

 Last reviewed: June 3, 1997
Article ID: Q109376
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
  • Microsoft Excel for Windows versions 4.x, 5.0, 7.0, 97

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

When you import a Microsoft Excel spreadsheet into a new table in a Microsoft Access database, you may receive the error message "Type Conversion Failure."

CAUSE

When you import a Microsoft Excel spreadsheet into a new table, Microsoft Access checks the first row of the spreadsheet to determine the data types for the new table. If the first row of the spreadsheet contains a numeric value in a column that allows alphabetic and numeric characters, Microsoft Access will define the field type in the table for that column as numeric. If there are alphabetic characters in other rows in this column in the spreadsheet, this error message will occur.

RESOLUTION

To work around this behavior, insert an extra row of data as the first row in the spreadsheet. Insert alphabetic characters in any columns that are defined as alphanumeric columns. Enter numeric characters in any columns that are defined as numeric columns. Once 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 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 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 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 alphabetic characters.

Keywords : IntpOthr IsmExl4 kbinterop
Version : 1.0 1.1 2.0
Platform : WINDOWS
Hardware : X86
Issue type : kbprb
Resolution Type : kbworkaround

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 3, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.