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

Joanchka

Registered User
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
 

Joanchka

Registered User
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
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
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

theDBguy

I’m here to help
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
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
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
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 (Users: 0, Guests: 1)

Top