How to import excel data into an existing table

[Guest]

I created a table by importing an excel worksheet into a new table, I now
have a new worksheet that I need to add to that table, but when I try to
import I am getting a vague error message: " an error has occurred importing
the file name.xls. The data was not imported". This is all the error
message states and the data is not imported. I cannot combine the two excel
worksheets and then import because their are over 68k line items. How can
you add to an existing table?

 

[John Nurick]

Hi Patricia,

This can happen unless the column headings in the two worksheets are
absolutely identical. Could that account for the problem you've had?

If the worksheets don't have column headings, you can import each to a
new table. But you can't import the contents of a second worksheet into
an existing table using the ordinary import routines (File|Get External
Data or DoCmd.TransferSpreadsheet. You can however create and execute an
append query that maps the field names as required; the SQL syntax is
like this, using "Field1" etc. to match the names in the table Access
created, and "F1" etc. for the columns in Excel.

INSERT INTO MyTable
(Field1, Field2, Field3, Field4, Field5, Field6, Field7)
SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9
FROM [Excel 8.0;Hdr=No;Database=C:\Folder\File.xls;].[Sheet1$];"