Solved

Import from Excel - Memo Field not accepting all text

Posted on 2014-10-31
15
580 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
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 84
ID: 40416442
Have you tried to link the Excel worksheet? If you do that, you can normally get the whole block of text.
0
 

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 49

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 49

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 49

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now