Microsoft KB Archive/237996

= ACC97: Error Importing Excel Worksheet with Mismatched or No Column Headers into Existing Access Table =

Article ID: 237996

Article Last Modified on 1/24/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q237996



Moderate: Requires basic macro, coding, and interoperability skills.



SYMPTOMS
If you try to use the Import Spreadsheet Wizard to import a Microsoft Excel worksheet into an existing Microsoft Access 97 table, you may receive the following error message:

An error occurred trying to import file ' '. The file was not imported.



CAUSE
You receive this error message if the worksheet does not have column headers, or if one or more column headers do not match the field names in the Access table.



RESOLUTION
Although the Import Spreadsheet Wizard in Access will not import an Excel worksheet with a mismatched column header or no column headers at all, you can use the following steps to create an append query to add the data from the Excel worksheet to the target Access table:  Count the number of columns (N) in the Excel worksheet. Make sure that the data that you want to import from the Excel worksheet is defined as a named range. Create a new query in Access. Do not add any tables to the query. On the Query menu, click SQL View.  Change the SQL statement to one of the following. Use the first example if you do not have column headings in the Excel worksheet. Use the second example if the column headings in the Excel worksheet do no match the field names in the Access table.   INSERT INTO [] () SELECT F1, F2, F3,...,FN FROM [EXCEL 8.0; HDR=NO; IMEX=2; DATABASE=<Path to Excel file>;TABLE=<NamedRange>].<NamedRange>;

-or-

<pre class="fixed_text">  INSERT INTO [<Dest. Table>] (<Table field names separated by commas>) SELECT <Worksheet column names separated by commas> FROM [EXCEL 8.0; HDR=YES; IMEX=2; DATABASE=<Path to Excel file>;TABLE=<NamedRange>].<NamedRange>; NOTE: F1 to FN should continue out to the number of columns being imported. Also, the order of the fields in the destination table field list must match the order of the unnamed fields in Excel. Replace the text inside the angle brackets (<>) with information about your Excel worksheet and Access table. The "<" and the ">" should be removed as well.

</li> Run the query. The data in the Excel worksheet should now be imported into the Access table.</li></ol>

<div class="references_section">