Solved

Memo cell cuts off characters when data is copied from excel

Posted on 2012-04-04
27
1,287 Views
Last Modified: 2012-04-12
I have created a table that gets it's data from a .xls file using a select all and cut from Excel 2010 and paste into my table in Access 2010. I have a cell that contains typed text that may be anywhere from 0 to a few thousand characters. In Access I set that field to memo which i thought would allow more than 255 characters.

My problem is that sometimes the memo cell will cut off the characters and not paste the entire contents. I can copy from Excel the single (for example) 500 character cell into Access and it will copy the full thing.

Any idea's how I can make the contents of the Excel file be mirrored in Access exactly?
0
Comment
Question by:Cole100
  • 12
  • 10
  • 5
27 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 37808811
did you try importing the excel file to Access?

External Data > excel , then follow the wizard
0
 

Author Comment

by:Cole100
ID: 37808815
That is how I set it up the first time. Each week I have 300 to 400 new records to add to the table so that is why I use the cut and paste
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 37808837
are these new records comes from a different excel file every week?


you can use vba command line to import the excel file

docmd.transferspreadsheet acimport,10, "TableName","c:\folder\myexcel.xlsx", true, "nameOfSheet!"
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)

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37811652
As a test, if you simply copy a big cell (one that you know gets truncated) and paste it to notepad, does it truncate?
0
 

Author Comment

by:Cole100
ID: 37813255
Using a vba command line is a bit outside my expertise. i am willing to give it a try just need more info on how to set it up.

i did try to use the import--->  append function in  Access and had the same results.

I tried a linked table and it cuts it off at 255 and will not let me change the field to memo.

I can paste the contents of the excel cell into notepad without isssue. I can even paste a cell that was truncated during the "mass" copy/paste into the cell in Access on an individual basis and it does not truncate it.

It appears to be random where it cuts off. See attached screenshot.
SnipImage.JPG
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 37813274
upload a copy of the db and the excel file.
0
 

Author Comment

by:Cole100
ID: 37813531
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37813823
The data is all there, it is just the the column is not wide enough to display it all.
This is why you should be using a form to view this data, not a table/query (datasheet view)
Create a Form from this table, then the resulting textbox for the Comment memo field should give you scrollbars to view all of the data.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37813842
In the form you can make the textbox bigger to get rid of the scroll bars.

In a report, you can set the "CanGrow" property of the textbox to: Yes
...and the textbox will get taller to display longer data.
0
 

Author Comment

by:Cole100
ID: 37813863
I think there is data missing. For example, ID 463 or 555 in the return_comment field it does not have the whole comment as it does in the excel file. I have tried the forms and reports and the same limited data in the table appears in the form/report.

see attached screenshot of "cutoff" fields
snip2.JPG
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 37813875
see this db, the excel file were imported using the codes behind the button Import

what is the respondent_id of these records   that you posted ID 463 or 555
ces.accdb
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 37813886
click on the button Open query, it will open query2 showing the comments and the number of characters in the comments field. you will see that there are records with more than 255 characters
0
 

Author Comment

by:Cole100
ID: 37813947
463 = 1773482429
555 = 1774832052

Issue appears in the return_comments not the comments. I used the len function (something new I learned, thanks) and that field max's out at 255

see attached...
snip3.JPG
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 37814005
try this one

open the same query


i'll explain later why this is happening...got to run outside for a while
ces.accdb
0
 

Author Comment

by:Cole100
ID: 37816524
So I see that the character count has increased but it still does not actually display the correct amount text.

What am I missing?
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 37816648
<still does not actually display the correct amount text.>  which one ?
0
 

Author Comment

by:Cole100
ID: 37817089
the query says that there are more characters then there actually is. see attached file for screenshot. you can clearly see that the words are being cut off
snip4.JPG
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37823575
capricorn1's suggestion of Appending the data via Excel import code should work for you.
It works just fine for me.

"Copy and Paste" is restricted by the clipboard.
It should not what is commonly used to import data like what you have.
0
 

Author Comment

by:Cole100
ID: 37824086
If I  use the append "wizard" it still  cuts it off for me. are you using the vba command line? if so, how do I do that?
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 37824173
did you download the file i uploaded at http:#a37814005 ?
0
 

Author Comment

by:Cole100
ID: 37824209
I did. Where do I set my source file path? I assume somewhere in here:

Private Sub cmdImportXL_Click()
Dim xlPath As String, xlFile As String
xlPath = CurrentProject.Path & "\"
xlFile = Dir(xlPath & "*.xls")
While xlFile <> ""

    DoCmd.TransferSpreadsheet acImport, 8, "myTable", xlPath & xlFile, True, "Sheet1!"


    xlFile = Dir
Wend
End Sub
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 37824250
change  this line

xlPath = CurrentProject.Path & "\"

with

xlPath="c:\folderName\"

c:\folderName\ is the folder where your excel file is
0
 

Author Comment

by:Cole100
ID: 37824423
Argh! So the import tool is working great. Cool new feature I can use. Thanks

Still getting the same thing though. Check out Query3. Shows cutting off  at 255

I really appreciate all the time spent working me through this
ces.accdb
04022012.xls
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 37824550
did you change the Data Type of the field to Memo?
0
 

Author Comment

by:Cole100
ID: 37824561
yes
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37824581
FWIW
This basic "TransferSpreadsheet" code that capricorn1 first posted works fine form me and does not truncate...

I'll leave this to him issue now...

JeffCoachman
0
 

Author Comment

by:Cole100
ID: 37824633
I just do not get it. It is clearly truncating for me. See attached screenshot
snip5.JPG
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

813 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

12 Experts available now in Live!

Get 1:1 Help Now