Importing an Excel file into a table with one field being a memo field

use concatenate and the restriction will be lifted.

Text values in formulas are limited to 255 characters. To create text values longer than 255 characters in a formula, use the CONCATENATE function or the concatenation operator (&).

I think to go around this quirk is to import into a temp table first and then append your data to the actual target table.

I did try importing to a temp table first. But when I looked at the contents of the field in the temp table only 255 characters were there.

I don't understand the CONCATENATE suggestion

I am not sure why it did not work for you using a Temp Table.
Are you sure you marked the Field Type Memo importing that field?

Couple of notes why this happens:

Source: Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or when Linking to an EXCEL File

The truncated text string that you see is because Jet (ACCESS) sees only "short text" (text strings no longer than 255 characters) values in the first 8 - 25 rows of data in the EXCEL sheet, even though you have longer text farther down the rows. What ACCESS and Jet are doing is assuming that the "text" data actually are Text data type, not Memo data type. One of these suggestions should fix the problem:


1) Insert a dummy row of data as the first row, where the dummy row contains a text string longer than 255 characters in the cell in that column -- that should let Jet (ACCESS) treat that column's values as memo and not text.

2) Create a blank table into which you will import the spreadsheet's data. For the field that will receive the "memo" data, make its data type "Memo". Jet (ACCESS) then will "honor" the field's datatype when it does the import.

That did not work.

Are you doing the import manually or are you using a Command function like DoCmd.TransferSpreadsheet() ?

Manually.

Although eventually I need to DoCmd.transferspreadsheet.

Yes.

You could save the Excel as an CSV file. Then import/link this.

/gustav

Gustav,

I had tried your suggestion but it didn't work.  But to be sure I'll try again and get back.

I just tried it again... saving the Excel file as a CSV Comma delimited (.csv) file and manually imported it into a test table.  The cell (field) with the character count over 255 stopped at 255 characters.

Very frustrating.

Have you tried just linking the worksheet? And/or inserting a very long text in the first row?

/gustav

I did try inserting a very long text in the first row but that didn't help.  And I can't use the method of linking to the worksheet because the intent is for outside people to send the Excel file to the office.  Then using DoCmd.transferspreadsheet append a table.

Then you may automation - open the Excel file and read the content of the range in question.

/gustav

As a test if anyone is willing to try it, I've attached an Excel file with the data "dummied" because I can't show the real data.  Plus a pretty much empty Access 2010 d/b.

I have tried over and over again to manually import the Excel file data into a new table and have all of the contents in cell B57 import properly and not cut off at 255 characters.  The Excel cell contains 1,077 characters and I sure wish I could make them all come over into the Access table.

--Steve
NotesToImport.xlsx
TestImport.accdb

Long text isn't a choice for me when I'm setting up the manual import.  I only have the following:

Plus I can't d/l your example file back to me.  When I click on it I get:

Standard ACE DBµnb` ÂUé©gr@?œ~Ÿÿ…š1Åyºí0¼ß̝cÙäßFûŠ¼N?Xì7äçœúý(檊`í7{6LÑß±ÎWCv ±3Ây[+|*£à|™˜ý-ãÏAI„f_•øЉ$…gÆ'DÒîÏeíÿÇF¡x íé-bÔT4.0
W
































































































































































































































































d
»v°:Àðÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ
Þ Y)
îîîîS 
++ Y  ì Y îY
 Y
Y  Y  îþ
Y  Y  î Y î Y î Y î Y þ
Y
2Ÿþ
Y
 Y  î Y  kþ
Y
ConnectDatabaseDateCreateDateUpdate FlagsForeignNameIdLvLvExtraLvModule LvPropName OwnerParentIdRmtInfoLongRmtInfoShortTypeƒ


ÿÿîÿÿÿÿîÿÿÿÿîÿÿÿÿîÿÿî
ÿÿÿÿîÿÿÿÿîÿÿÿÿîÿÿÿÿîÿÿ»´Y

ÿÿÿÿ
YÿÿÿÿIdParentIdName        ÿÿ
©O
Y|
Ïi!&S



+Y

Y

Y  Y
2pþ
ACMFInheritableObjectIdSIDƒ
ÿÿXÿÿÿÿÿÿNÿÿÀÿÿÿÿýÿÿ•ÿÿ¸ 5&€ˆYÿÿÿÿObjectIdÿÿ
ë Y
S


Y

Y Y Y  Y
þ
Y þ
Y  Y þ
AttributeExpressionFlagLvExtra Name1 Name2ObjectId Orderƒ



ÿÿÿÿÿÿÿÿÿÿÿÿÿÿ Yÿÿÿÿ
"ObjectIdAttribute  ÿÿ
Ë -Y
S
SS
Y
Y

Y
 Y j þ
Y
þ
Y þ
Y ¦þ
Y þ
ccolumn

Pick Memo.

Right-click file, Save As, change file extension from html to accdb.

/gustav