Import Errors Table, unparsable record

[Guest]

When importing an Excel spreadsheet into Access (both programs are the versions that came with XP Professional), the import was partially successful, but an "Import Errors Table" was generated due to many "Unparsable Records". In MS Access Help, it says this is often due to Text Delimiters, such as double quotation marks. But there were no such delimiters in the identified error locations. The errors were in many of the same fields for almost all rows, beginning with row 1. Another post suggested this may be due to character limits per record. Are there such limits in Access XP? Or could this be happening for another reason?

 

[John Nurick]

Hi Liz,

Access does have a limit of approximately 2000 characters or 4000 bytes
per record, excluding the contents of memo and OLE fields, and a wide
Excel table can easily exceed this.

(You can circumvent it either by creating the Access table manually,
using memo fields for the wider Excel columns of text, or by linking to
the spreadsheet and using queries to move the data into two or more
narrower tables).

Otherwise, the cause is almost certainly something to do with the data
in your worksheet. There's some infromation here about how to interpret
the Import errors table
http://office.microsoft.com/assista...ID=HP051885461033&CTT=4&Origin=CH063648351033


Also, the import routine often has trouble with hidden columns, and
sometimes with cells that contain formulas. Make sure there are no
hidden columns, and if there are formulas try getting rid of them by
selecting the entire table and doing Copy and then Edit|Paste Special
and selecting Values.

When importing an Excel spreadsheet into Access (both programs

are the versions that came with XP Professional), the import was
partially successful, but an "Import Errors Table" was generated due to
many "Unparsable Records". In MS Access Help, it says this is often due
to Text Delimiters, such as double quotation marks. But there were no
such delimiters in the identified error locations. The errors were in
many of the same fields for almost all rows, beginning with row 1.
Another post suggested this may be due to character limits per record.
Are there such limits in Access XP? Or could this be happening for
another reason?

 

[John Nurick]

Liz,

The contents of memo and OLE fields are stored elsewhere in the MDB file
and the record itself just contains a "pointer", so they avoid the
record size limit. In general memo fields in Access's Jet database
engine behave just like text fields except that they can't be indexed,
they're slower to search, and using things like the Format property
tends to truncate them at 255 characters long.

Hi John,

Thanks so much! It looks like it is, in fact, the character limit that

is causing these errors. I am going to try switching these longer text
fields to memo fields. But I was wondering, will this affect future
imports into this table? That is, will text columns from Excel (or a
text file) be imported into Access memo fields in the same way they
would into Access text fields? I guess it is just not clear to me
whether memo fields are stored differently to allow for the much greater
capacity.