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

I believe this was discussed a few days ago but I am really stuck.  I have an Excel file which contains a cell that has more than 255 characters.  I need to be able to import the entire contents of that cell into a table field.  But no matter what I try it only imports the first 255 characters even through the field in the table it is being imported into is a memo field.

Can someone help?

--Steve
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamCommented:
use concatenate and the restriction will be lifted.
0
ProfessorJimJamCommented:
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 (&).
0
danishaniCommented:
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.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

SteveL13Author Commented:
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.
0
SteveL13Author Commented:
I don't understand the CONCATENATE suggestion
0
danishaniCommented:
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.
0
SteveL13Author Commented:
That did not work.
0
danishaniCommented:
Are you doing the import manually or are you using a Command function like DoCmd.TransferSpreadsheet() ?
0
SteveL13Author Commented:
Manually.
0
SteveL13Author Commented:
Although eventually I need to DoCmd.transferspreadsheet.
0
danishaniCommented:
Its hard to tell why its not working.
Did you try to have the first row more than 255 characters, in order to see if that helped?
0
SteveL13Author Commented:
Yes.
0
Gustav BrockCIOCommented:
You could save the Excel as an CSV file. Then import/link this.

/gustav
0
SteveL13Author Commented:
Gustav,

I had tried your suggestion but it didn't work.  But to be sure I'll try again and get back.
0
SteveL13Author Commented:
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.
0
Gustav BrockCIOCommented:
Have you tried just linking the worksheet? And/or inserting a very long text in the first row?

/gustav
0
SteveL13Author Commented:
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.
0
Gustav BrockCIOCommented:
Then you may automation - open the Excel file and read the content of the range in question.

/gustav
0
SteveL13Author Commented:
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
0
Gustav BrockCIOCommented:
If you have a long text as (one of) the first row(s), you can both import and link the worksheet.
Choose "Long Text" for the notes field.

/gustav
NotesToImport.xlsx
TestImport.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
Long text isn't a choice for me when I'm setting up the manual import.  I only have the following:

Choices
0
SteveL13Author Commented:
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.0Wd»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        ÿÿ©OY|Ïi!&S  +Y Y Y  Y 2pþACMFInheritableObjectIdSIDƒÿÿXÿÿÿÿÿÿNÿÿÀÿÿÿÿýÿÿ•ÿÿ¸ 5&€ˆYÿÿÿÿObjectIdÿÿë YS  Y  Y Y Y  Y þ Y þY  Y þAttributeExpressionFlagLvExtra Name1 Name2ObjectId Orderƒÿÿÿÿÿÿÿÿÿÿÿÿÿÿ Yÿÿÿÿ"ObjectIdAttribute  ÿÿË -YSSS  Y Y Y  Y j þ Y þ Y þ Y ¦þ Y þccolumn
0
Gustav BrockCIOCommented:
Pick Memo.

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

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.