Solved

Import from Excel - Memo Field not accepting all text

Posted on 2014-10-31
15
692 Views
Last Modified: 2014-11-02
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
0
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 5

Expert Comment

by:ReneD100
ID: 40416322
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40416365
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
 
LVL 85
ID: 40416442
Have you tried to link the Excel worksheet? If you do that, you can normally get the whole block of text.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:pdvsa
ID: 40416537
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
 

Author Comment

by:pdvsa
ID: 40416542
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
 

Author Comment

by:pdvsa
ID: 40416546
must have been the name I had on that file that EE didnt like.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40416629
Tony, if I import it using A2013 and specify Long Text (memo) for the field, it is retrieved in full.

/gustav
Memo.accdb
0
 

Author Comment

by:pdvsa
ID: 40416805
Hi GUSTAV, that sounds like a winner.  Not at computer now though but plan to test in the am.  Thank you
0
 

Author Comment

by:pdvsa
ID: 40417379
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
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40417626
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
 

Author Comment

by:pdvsa
ID: 40417658
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
 

Author Comment

by:pdvsa
ID: 40417660
Sorry, i hit send...

If Rene or someone else can give some feedback in regards to vb net I would appreciate...
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40417947
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
 

Author Comment

by:pdvsa
ID: 40418115
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
 

Author Closing Comment

by:pdvsa
ID: 40418398
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

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question