Importing Excel97 data to Access-not all records imported 2

[lmorgan]

Good evening!

I seem to be having a problem with importing files from Excel97 to Access97 where the number of records is more than 16,383. It will pull only the first 16,383 records and not the remaining 4,000 or so.

I tried to open up the Excel file while importing and received an Import Error message, so that doesn't seem to be the solution.

I'm certain there has to be an easy answer to this. Can anyone shed some light on how to get around this ?

Thanks so much!
Lisa

 

[dawnd3]

The remaining 4000 are breaking some sort of rule you have set in a table. Perhaps the dates are not formatted correctly or the unique ID is duplicated. Quite often, the problem I run into is that when records in Excel have a blank field, Access sees it as "Null" rather than just blank. So, in the table design I will change all of the fields to "Allow zero Value = yes" and importing works perfect. Try that and see if it helps. But first off just make sure your spreadsheet is cleaned up. (text feilds are text, date feilds are date, and so on)

Dawn

http://www.calliopeconsulting.net

 

[zerosandones]

Hi Lisa,

When I read your symptom, I vaguely recalled seeing it many years ago. i tracked down the relevant article in Microsoft's Knowledge Base.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q167669

P.S. If you want 100% reliable imports in the future from Microsoft Excel into Access, try the following:

1) Your Excel spreadsheet will likely have column headings. If not, create them and do yourself a favour ... do not include spaces in your field names or any other special characters that might be special reserved characters in Access;

2) Save the spreadsheet as a "Tab-delimitted text file". The result is a flat text or ASCII file;

3) Import it into Access, selecting Delimitted, choose a (" double-quote) text-qualifier, make sure that you check "first row includes column headings", ensure that every field's datatype is changed to "text" and ignore generation of any primary key;

In doing so, you avoid "all" the pitfalls of complex file decomposition which occurs in files such as Excel. It also, conveniently, avoids the symptom you originally described in your first post.

Hope this helps.