Solved

Import from Excel - Memo Field not accepting all text

Posted on 2014-10-31
15
598 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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
 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

773 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