03 Access output to Excel truncates data

[DonaldCollins]

Hi Community,
We have an Access "Long text" data type field.
In the Access report, all the data in the field is displayed.
When the data is exported to excel, the field is truncated.
What is weird is that the truncation in excel does NOT start at 255 characters, but more downstream so to speak.
For example, in the table and the report a long text field record has 15,460 characters.
But when exported to excel it contains only the first 13,212 characters and truncates the remaining 2248 characters of the long text field.
Please advise! 
Thank you for your time.
Best Regards,
Donald

[#2]

You do not say what version of Excel you are using. Excel actually has quite a long limit more like 32,000 to the definition of what can be stored in a cell, however what actually displays in a cell can be less that that for older versions (2003 and older). In more current versions it should be the same. Can you copy and paste the value manually from Access to the Excel result and then see the whole result?

[#3]

Hi Maria,
Thank you for your help.
Sorry - you're right I should have included that - they're both 365...
MS Excel for MS 365 MSO (16.01....) 64-bit
MS Access for MS 365 MSO (16.01...) 64-bit

Excel's 32k cell limiit - that's the other oddity as you found too, the cell has plenty of room to hold the data coming from Access.
Yes, we can copy and paste manually form Access to Excel, no problems.
But when this Access form button exports table data to excel , it has truncation.
Perhaps that is where the issue is ? At the form/macro./vb layer?
Best Regards, Donald

[#4]

Thanks Donald. Can you explain exactly how you are exporting to Excel? Are you opening the report in Print Preview mode and then using the Excel export on the Data section of the ribbon?

[#5]

Hi Maria,
I asked the developer and it seems that is how is exported.
This is her reply;
"
Yes, the button on the report forms opens print preview and then click on excel from there. There’s probably a “prettier” way to do this but since I don’t have that knowledge, I went with what worked.
"
Best Regards,
Donald

[#6]

I talked to one of the Access Team and this is what he said:
When exporting to older Excel formats (Excel 97-Excel 2003 or older), we export a maximum of 255 characters.
When exporting to current Excel formats, we export more than 13000 characters.
When using the Excel button from Report Print Preview, we only give you the option to export to the older formats (because the code for exporting reports was never updated for the newer Excel formats, only the code for exporting tables)

So, I’m not sure how you get the case where we are exporting >255, but <15000 characters.

If you can give me a database that shows what you are seeing, and information about version/build of Office, I can look further.

If not then you may be able to get around this by exporting directly from the table data instead.

[#7]

Hi Maria,  
Thank you for you help - there was a bit of back and forth last week internally within the regulatory group regarding sending you a db with the example of the issue ie, " If you can give me a database that shows what you are seeing, and information about version/build of Office, I can look further. ".  
I am not sure how they want to proceed at this time.
I am suggestioning a bare-bones db with just the problem record and output as a test case.
I hope to get back with something mid-week.
Thanks!
Best Regards,

Donald

[#8]

Hi Maria,
Thank you for your patience.
We have put together a working test case build that demonstrates the truncation issue.
It is attached as "truncation_for_description_field.zip"
Below are screenshots (also in zip file) that step one through the process from opening up the db to exporting the report to excel to show the truncation.
Best Regards, Donald

[#9]

@DonaldCollins Perfect, will pass this on!

[#10]

@MariaBarnes thank you!

[#11]

Hi Maria, 
Hope all is well. 
Has there been any feedback from the MS team about the test case and issue? 
Thanks!
Best Regards,
Donald

[#12]

@MariaBarnes 

Hi Maria, 
Hope all is well. 
Has there been any feedback from the MS team about the test case and issue? 
Thanks!
Best Regards,
Donald

[#13]

Sorry Donald. I was on vacation last week. On 4/5 the team did respond to me. They say "there are some issues here we need to investigate. I can’t give a timeline for addressing them, but I can say that I was able to reproduce the problem, and that we will investigate." That may not be exactly what you are looking for, for the short term, but the fact that they can easily reproduce is a good indicator that it will get fixed.

[#14]

@MariaBarnes 
Hi Maria - no problem at all, I hope you had a good holiday!
We are glad that they were able to re-produce issue and are investigating it further.
Thank you for following up we are hopeful a fix or workaround is possible.
Best Regards,
Donald

[#15]

@Maria Barnes

@DonaldCollins 

Hi Maria - Hope all is well.  Is there any word/update from the internal team ?

Thanks!

Best Regards,
Donald

[#16]

Not yet, no. Reading between the lines of their answer I would guess 6 months or so to expect a response.

[#17]

Okay thank you for the update !

Best Regards,
Donald