Can't import Excel data into Access

[browniebodrum]

Having trouble with one particular Excel 2003 spreadsheet using 'Get External
Data'. Have tried importing named range and/or worksheet, with and without
column heading,s with and without indexing and with and without primary key
but get an unspecified error each time ('File cannote be imported' or similar
but no information on why). Tried using 'paste append' but only first 10
columns out of 14 imported. Tried simple copying and pasting but only first
21 records imported. Data is simple text/numeric. Can't spot anything odd.
Other spreadsheets import ok. Please help - it's driving me mad and holding
me up. Access tables view says 'Access 2000 file format' - can't remember
whether/how this is significant.

 

[Jeanette Cunningham]

browniebodrum,
this seems to be a recurring problem - it is due to the nature of excel
spreadsheets.
From your post, it appers that the first 21 records in the spreadsheet will
import OK.
If you create a new spreadsheet using only the first 21 records, they will
probably import into access.
It the test above with the first 21 rows goes smoothly, this suggests there
is something about row 22 and maybe further down that have a format or
formula that is affecting the import. Maybe there are some hidden columns or
extra blank cells with formula or formatting that won't import.
Hope this helps.
As a last resort, save the file in csv format and import that.

Jeanette Cunningham

 

[browniebodrum]

Thanks, Jeanette, the csv option imports fine. I think the problem is/was
with the column headings, which are: Account, Company, ADM, ASS, CNS, EVT,
IAC, INS, ITC, ITT, LEA, MEM, WEB. When I exported the data without the first
row, it went across ok and then I renamed the Fields in Access. One of them
had a stray space character in it in the Excel version, so maybe that was
causing the problem. I'll remember the csv trick in case it happens again.