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

• Microsoft Access 97

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 '<filename>'. 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:

1. 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.



2. Create a new query in Access. Do not add any tables to the query.



3. On the Query menu, click SQL View.



4. 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 [<Dest. Table>] (<Table field names separated by commas>) 
   SELECT F1, F2, F3,...,FN
   FROM [EXCEL 8.0; HDR=NO; IMEX=2; 
   DATABASE=<Path to Excel file>;TABLE=<NamedRange>].<NamedRange>; 

-or-

   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.


5. Run the query. The data in the Excel worksheet should now be imported into the Access table.

REFERENCES

For more information about about append queries, click Contents and Index on the Help menu, click the Index tab in Access Help, type the following text

append queries, creating

naming, ranges

Additional query words: prb sheet spreadsheet

Keywords : kbdta ocsso
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb