Force Access to import Excel column as a memo fieldtype

[Guest]

Hi all

I have an excel file with a particular column which contains more than 255
characters in certain fields. When I try to import this file, the import
wizard greys out the field type so that I can't change it from text to memo.
Consequently, that column is truncated. How do I force Access to import this
particular column correctly (i.e. not cutting everything off)?

Thanks for your help

 

[John Vinson]

Hi all

I have an excel file with a particular column which contains more than 255
characters in certain fields. When I try to import this file, the import
wizard greys out the field type so that I can't change it from text to memo.
Consequently, that column is truncated. How do I force Access to import this
particular column correctly (i.e. not cutting everything off)?

Thanks for your help

I'm not certain that it will work in this case, but I've taken to
routinely creating an Access table with the desired field types and
sizes; and then *linking* to Excel and running an Append query to
populate the table. Since Excel does not have strong datatyping,
Access must guess at the appropriate field type - and often guesses
wrong.

John W. Vinson[MVP]
(no longer chatting for now)

 

[Ken Snell [MVP]]

You appear to be using the manual "Import" process. Instead, use
TransferSpreadsheet action in a macro or VBA code. TransferSpreadsheet
allows use of a newer version of EXCEL that will properly handle long text
strings. The manual process uses EXCEL 95 format, which does not handle long
strings.

 

[Damien McBain]

Hi all

I have an excel file with a particular column which contains more
than 255 characters in certain fields. When I try to import this
file, the import wizard greys out the field type so that I can't
change it from text to memo. Consequently, that column is truncated.
How do I force Access to import this particular column correctly
(i.e. not cutting everything off)?

Thanks for your help

I save my Excel table as a csv, then import the csv into access. This gives
me the option of specifying the data typre for every field and changing
field names etc. This is OK if you just want to shove some data into access
from another application.

If you're doing it all the time with the same format then Ken or John's
solutions are probably more appropriate.

Damien