Import from Excel - Memo Field not accepting all text

Experts, I am importing from excel.  The field "Deliverable" in my Access table is formatted as a memo field but it still doesnt hold all of the text from the imported excel file with the column name "Deliverable".  The "Deliverable"column in excel is formatted as text and you can see from the below pic, the matching field "Deliverable" in the Access table is formatted as Memo and I would think it would accept all the text.  

why is the field in my access table "Deliverable" not accepting all the text?  I think its being cut off at 256 and that is the limit for Text.  Memo should be unlimited. The text in the excel column "Deliverable" is not too much more than 256 (probably a max of no more than 500) so I dont think I am running into some type of limit for Memo (if there is one).  

fyi I have deleted the field and added it and deleted the table and recreated it but it still doesnt import all the text.

let me know if you see something wrong with the pic or have any other suggestions.
thank you

Memo field
pdvsaProject financeAsked:
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.

ReneD100Commented:
The problem is not in the table bit in the transfer - only 255 characters are used. In the past I've had the same problem exporting back to Excel.
I solved it by using an small tool in vb.net to transfer the content row-by-row from Access to Excel, but the other way around would work as well.
0
Jeffrey CoachmanMIS LiasonCommented:
If you are very careful, ...you should be able to simply "Copy" this Excel "List Data", and paste it directly into a new access table.

In Excel, select only the cells that contain your data.
Select: Copy
Open Access
Right click in Access and select:  Paste

This works fine for me, ...so it might be a good option if this is a one shot deal...

JeffCoachman
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Have you tried to link the Excel worksheet? If you do that, you can normally get the whole block of text.
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!

pdvsaProject financeAuthor Commented:
Rene, thank you but that might be something I cant install on this computer as it locked down.  Let me know if its something that doesnt require installing. It might be too "techy" for me though.  I am not an expert.

Jeff:  I have tested the copy and paste entire data set as you mention but that doesnt seem to fix the issue.   I created a new table with only that field and pasted but it still cuts it off.  I also tested on the table I am importing into.  Both ways it did not allow all the text.   In the new table I created to test, I only have ID and Deliverable (memo) and it did not accept all the text.  

Scott: Linking does not work either.  I thought for certain it would.  It cuts off at 256 just like the import.  

I think the data might be needed.  I have attached an excel of only the particular column that is not importing all the text.  I have highligted the ones red that have >256 characters.  There are duplicates of rows and that should not matter.  

However, I can copy and paste the data in a cell separately and it does work.  Its just the import and the copy and paste of the entire data set that doesnt.  

let me know what to do....it is driving me nuts!
Data-not-importing-entire-contents-of-ce
0
pdvsaProject financeAuthor Commented:
i dont know what is wrong with that file.  It doesnt seem to want to save as an excel...let me see if I can change the name of the file and upload again.
Data.xlsx
0
pdvsaProject financeAuthor Commented:
must have been the name I had on that file that EE didnt like.
0
Gustav BrockCIOCommented:
Tony, if I import it using A2013 and specify Long Text (memo) for the field, it is retrieved in full.

/gustav
Memo.accdb
0
pdvsaProject financeAuthor Commented:
Hi GUSTAV, that sounds like a winner.  Not at computer now though but plan to test in the am.  Thank you
0
pdvsaProject financeAuthor Commented:
Gustav, at computer now.  I do not have 2013.  I have 2010.  I have attached a screen print.  I am not sure how I can set the property to Long Text for 2010 but maybe it is not an option in 2010?  Please see the attached pic. I do not have any options for "format" on a memo and I assume this is where I would see the Long Text format for a memo field.

thank you

Memo Field
0
Gustav BrockCIOCommented:
Tony, there is no format to set. Memo was simply rebranded as "Long Text" in A2013.

I don't have A2010 at hand, so I can't tell if there is some misbehaviour in A2010 when importing as Memo from an Excel sheet that has been corrected in A2013.

/gustav
0
pdvsaProject financeAuthor Commented:
GUSTAV, ok.


Correct me if I am wrong but it seems like 2010 will not import the cell .  completely.  

Rene did mention vb net as a possible solution.  Any experience with this
0
pdvsaProject financeAuthor Commented:
Sorry, i hit send...

If Rene or someone else can give some feedback in regards to vb net I would appreciate...
0
Gustav BrockCIOCommented:
Tony, I got access to an A2010 install, and it behaves correctly.
I suspect you don't specify the field in question as Memo in the import wizard:
Import wizard/gustav
MemoImport.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
pdvsaProject financeAuthor Commented:
GUSTAV, thank you.  I will test when I get to a computer.  When I import, I did not set the property as shown in your pic.  I only set the property in the table fields.
0
pdvsaProject financeAuthor Commented:
Perfect!  The import is complete.  I guess that setting the field in the table is only half of what is needed.  In addition, and as you stated, the field imported must be set to Memo as well within the import wizard.  Thank you.
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.