Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Import from Excel - Memo Field not accepting all text

Posted on 2014-10-31
15
Medium Priority
?
776 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 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 52

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 52

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 52

Accepted Solution

by:
Gustav Brock earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

926 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