Question Export to Excel truncates data at just under 4k characters (1 Viewer)

Joanchka

Registered User.
Local time
Today, 23:55
Joined
Aug 31, 2018
Messages
19
I have a Long Text field called EbayDescription which can contain up to around 30k characters of html (always under 32k)

When I try to export using External Data > Export to external spreadsheet this field get truncated to just under 4k characters.
It isn't an exact number as record 1 gets truncated after 3,987 characters (it should have 25,882 characters) and record 2 gets truncated after 3,886 characters (it should have 4,940 characters)
I can't see anything in common twith the two cut off points. I get the same problem if I right click the table or query and try exporting from there.

Exporting as a text file isn't an option as although it does export all the content, format is lost as my content contains line breaks.
It will also export okay as an rtf file but again I can't convert that to an Excel file without issue.

I've searched quite a bit on this but most of the truncating problems refer to a 255 character limit.

I'm hoping that someone can shed some light on this for me.

I'm using Access 2013 and do everything with queries and have never used vba or sql
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:55
Joined
Oct 29, 2018
Messages
21,358
Hi. Are you able to provide some sample data for testing?
 

Joanchka

Registered User.
Local time
Today, 23:55
Joined
Aug 31, 2018
Messages
19
Hi. Are you able to provide some sample data for testing?


What's the best way to supply the data as I can't export to Excel?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:55
Joined
Oct 29, 2018
Messages
21,358
What's the best way to supply the data as I can't export to Excel?
I imagine you have it in an Access table? If so, can you post the Access file?
 

Joanchka

Registered User.
Local time
Today, 23:55
Joined
Aug 31, 2018
Messages
19
Okay, I've created a new blank database and put a table in it with some data
 

Attachments

  • Test1.accdb
    472 KB · Views: 119

theDBguy

I’m here to help
Staff member
Local time
Today, 16:55
Joined
Oct 29, 2018
Messages
21,358
Okay, I've created a new blank database and put a table in it with some data
Hi. Thanks. I have exported the table data into Excel. Can you please verify it contains all the chars? Thanks.
 

Attachments

  • Query1.zip
    26.9 KB · Views: 289

Joanchka

Registered User.
Local time
Today, 23:55
Joined
Aug 31, 2018
Messages
19
Wow! Yes, it is all there. I've been trying to do that for the last 2 days.


How did you manage it?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:55
Joined
Oct 29, 2018
Messages
21,358
Wow! Yes, it is all there. I've been trying to do that for the last 2 days.

How did you manage it?
Well, I don't know if it makes any difference, but I wanted to find out how many characters we're dealing with, so I can verify if it gets truncated. So, I created a query to display all records and added a calculated column to show the number of characters. After I saw it, I simply exported the query to Excel. I will try exporting the table directly to Excel to see if there's a difference and let you know.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:55
Joined
Oct 29, 2018
Messages
21,358
I will try exporting the table directly to Excel to see if there's a difference and let you know.
Well, I guess not. So, there must be something in how you're doing the export on your end. How exactly are you doing it? I just right-click on the table and selected Export.
 

Attachments

  • tblDescriptions.zip
    26.8 KB · Views: 124

theDBguy

I’m here to help
Staff member
Local time
Today, 16:55
Joined
Oct 29, 2018
Messages
21,358
Interesting that your spreadsheet is different to mine, ie your records just occupy one line but mine shows all the line breaks etc
I think that's just a matter of display formatting. If you select all the cells in the Excel file I gave you and then go to the Home tab on the Ribbon and select/click on Wrap Text in the Alignment group, you should see the line breaks too (after you resize the cells, of course).
 

Joanchka

Registered User.
Local time
Today, 23:55
Joined
Aug 31, 2018
Messages
19
I wonder what the difference is between what you are using/doing compared to me.
I've tried the test db on 2 different computers (both using Access 2013).
I've tried exporting both from right-click on the table and also from the Export Ribbon.
I've also tried opening the exported file in both Excel 2010 and Google Docs Spreadsheet.
Everything I try gives the same truncated data result
 

Joanchka

Registered User.
Local time
Today, 23:55
Joined
Aug 31, 2018
Messages
19
Okay, I've eventually found the solution that will work for me (just in case anyone else has this problem in the future)
Export from Query (any option) - data truncated
Export from Table (with 'Export data with formatting and layout' selected) - data truncated
Export from Table (with 'Export data with formatting and layout' NOT selected) - This creates 2 worksheets when opened in Excel. The first worksheet has data truncated but the 2nd worksheet has ALL DATA


I would like to thank @theDBguy for helping me with this
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:55
Joined
Oct 29, 2018
Messages
21,358
Okay, I've eventually found the solution that will work for me (just in case anyone else has this problem in the future)
Export from Query (any option) - data truncated
Export from Table (with 'Export data with formatting and layout' selected) - data truncated
Export from Table (with 'Export data with formatting and layout' NOT selected) - This creates 2 worksheets when opened in Excel. The first worksheet has data truncated but the 2nd worksheet has ALL DATA

I would like to thank @theDBguy for helping me with this
Hi. Congratulations! Glad to hear you found a working solution. Good luck with your project.
 

Users who are viewing this thread

Top Bottom