Importing Data into a new Table

[Jason Nelson]

Greetings

I am trying to import a spreadsheet (02 format w/2800
rows) into a new Table and am getting an error saying:

"An error occured while trying to import the
file 'c:\example.xls'. The file was not imported."

I have always updated a certainn table this way and now
all of a sudden it is not working. I have:

*Checked the spreadsheet for errors
*Tried parting all the data into a new one
*Imported other spreadsheets succesfully
*Tried importing from a different PC

Same error message

I usually overwrite the old table with the new one, and
because of this Access deleted the old one and now we
have nothing. This is VERY frustrating as the message is
not specific and my sales department is unable to use the
database. Please help!

Jason Nelson

 

[Bob Harding]

Is the sheet you are trying to import the first sheet in
the workbook in excel? I know that mailmerge has problems
with this. Try deleting other sheets within the workbook.

Look for spurious data on the sheet you're importing
select some blank columns and blank rows and delete them
(they may contain spaces).

I'm no expert (as you can tell) these are just a few
ideas that came to me

Bob

 

[Adrian Jansen]

Access makes assumptions about the datatype in Excel based on the first few
rows in the sheet. It doesnt matter what the table field types are, if
Access decides a column from the sheet contains a number, it will cause an
import
error, even if the field its going to is text.

The only easy work around I have seen is to place at least one alpha
character in the first row of the sheet, in each column, which forces Access
to treat it as text. Import the sheet into a temp table with all its fields
as text, then use a query
to append the records you want into your final data table, and fix up any
data typing etc within the query.

The other advantage of importing into a temp table, is that you dont lose
your main table until you know the import has worked successfully.
--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[Immanuel Sibero]

Hi Jason,

- How are you importing it? Whole worksheet? Or a named range?
- Have you tried linking to it? Link as a whole spreadsheet? or a named
range?

Immanuel Sibero

 

[Tim Ferguson]

Access makes assumptions about the datatype in Excel based on the
first few rows in the sheet. It doesnt matter what the table field
types are, if Access decides a column from the sheet contains a
number, it will cause an import
error, even if the field its going to is text.

The most reliable solution is to create the table in Access first, ensuring
that you make the correct field types and putting on the Primary Key
constraints, Validation Rules, etc. Then append the data from the Excel
sheet into the new table.


HTH


Tim F