Errors during import of spreadsheet

[crowegreg]

I can't figure out why it's truncating some of the records. Can you figure out what the problem is?

[JoeM]

For security reasons, I am unable to download files off the internet, but if you describe your problem in detail, I may be able to help out.
Off the top of my head, one of the first things to check is how the fields in the table you are importing to are formatted. If you import a decimal field into a field that is formatted as Integer, obviously those decimals will be truncated.

[crowegreg]

Thanks for replying!

It's really weird. This file is created by Microsoft's MapPoint application with an add-in. If I create this file with 40 records, its works properly. If I create it with 50 records, the import processes the first 41 records correctly, then any records after 41 it produces an error table and in the explanation, it shows it truncated the record. I created the file mulitple times to make sure it wasn't a freak happening. I tested the file so that if anyone uses the standard excel import within Access 2007/2010, they will hopefully get the same results as I received.

[JoeM]

Are you importing it into a new table, or an existing table?
What kind of file is it (text, Excel, etc)?
Do you use an Import Specification?
If you look at the 41st and 42nd records in your import file, do you notice anything different or odd about them (there aren't any breaks or odd characters, are there)?

[crowegreg]

A new table
excel
no import specification
no difference in the records

[JoeM]

The issue with importing Excel files into new tables is that Excel and Access try to communicate with each other and "figure out" what format wach field should have. I believe it looks at the top ten records.

So if you have a field where there are no decimals in the top ten records, but decimals do appear in that field further on down, Access is probably going to assume an Integer format, which will truncate decimals.
Likewise, if your have a field that has only numbers in it on the first ten records (maybe some sort of ID), and then further IDs down the page have letters in them, Access is probably going to pick a number format, causing the entries with letters to error out.

Personally, I hate importing Excel files into new Access tables - you have very little control over the process. I prefer to import the Excel files into EXISTING Access tables where you have already pre-defined the format of all the table fields. Then you are taking the Excel-Access guess work out of it.

A long time ago, I had some Excel imports that I just couldn't get to work at all. In that case, as a last resort, I opened my file in Excel and saved as a text file (CSV or tab-delimited), and then imported that text file into Access where I have full control over the whole import process.

[crowegreg]

I've just done some more testing, within my spreadsheet, if I remove all the records that successfully are imported, so now the excel spreadsheet's first record is the record that produced the error, it imports successfully. Within the original spreadsheet error is produced at column "CL". So that is column number 64. Does Access have limitiations on the import with the number of columns in a spreadsheet.

[JoeM]

I believe the limit is 256 fields.

[crowegreg]

I'm under 256 fields, so I wonder what's causing the problem.

[crowegreg]

Here's what I've determined in my testing, if I link the spreadsheet rather than import, it works.