Exporting Excel to Access - formating text box to memo

[Guest]

I export information from an excel file into an access table which I then run
reports off. The problem is, when I bring the excel information into a new
table, for some reason, one of the cells refuses to format as a memo cell;
instead, it keeps formating as a text cell cutting off the information after
a certain number of characters. I try to save the table with the cell noted
as memo, but every time I delete the table and import the updated information
into the replacement table, the cell has reverted back to text. Any ideas?

 

[Ken Snell \(MVP\)]

How are you importing the data -- File | Get External Data ... | Import ?
Or TransferSpreadsheet (in VBA code or in macro)?

The former will cause truncation; the latter should avoid truncation.

 

[Guest]

I am not familiar with 2007, yet, so I may be talking out of turn, but if
<=2003, there should be no problem. The maximum for an excel cell is 255
characters and the maximum size of an Access table field is 255 characters,
so there should be no truncation.... Unless (I haven't tested this, it is
only food for thought), the Default Field size setting on the Tables/Queries
tab of the Options dialog has any effect on this problem. The other fix may
be to save the xls file as a csv and create an import spec for it with the
field length set to 255.

 

[Ken Snell \(MVP\)]

Actually, you can put 32,767 characters into a single EXCEL cell for 97 -
2003.

The problem with using the File method for importing is that it uses the
EXCEL 95 format for the data -- and EXCEL 95 did not support text strings
longer than 255 characters. A similar problem exists if you use File to
export or use OutputTo action (VBA or macro). TransferSpreadsheet can use
the newer EXCEL versions' formats, and thus supports memo-long strings.

Another issue that the poster may be having is if the "long" strings are not
found in the first 8 (or 25, depending upon the poster's PC's registry key
setting value) rows. ACCESS / Jet will decide that the data are text and not
memo, and truncation then can occur even if you use TransferSpreadsheet.

 

[Guest]

Doh!, right. I check the Excel specs (2003) and read column width max is 255.