Exporting a query from Access to Excel causes fields to be truncated to 255 characters.

Question

_{Monday, February 19, 2018 11:23 AM}

I have an 'Export to Spreadsheet' button in my DB, and when pressed it should export the result of a query to an excel spreadsheet. However the long text fields are not being completely exported and only the first 255 characters are in the spreadsheet fields.

My button uses the 'ExportWithFormatting'  MacroBuilder option on the 'on click' event.

I have also tried running instead the following VB lines at the 'on click' event:

Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12Xml, "qryNormalChangesExporter", "C:\Temp\TESTTRANSFERSPREADSHEET", True)

DoCmd.OutputTo acOutputQuery, "qryNormalChangesExporter", acFormatXLSX, "C:\Temp\TESTOUTPUT.xlsx"

However the long text field is still truncated to 255 characters in the spreadsheets.

I have noticed that the data type of the column in the spreadsheet is 'General'. Is this limited to 255 characters?

If I change the data type of the column to 'Text' and copy and paste the field, all of the characters are pasted into the spreadsheet cell.

Nath

All replies (32)

_{Thursday, February 22, 2018 3:24 PM ✅Answered | 1 vote}

I downloaded and looked at it, and it's as I originally suspected, and you denied: the Format property of the Description field is set to "@" in both the table and the query.

However, removing the Format wasn't enough to fix the problem..  Looking even closer, I found that the Text Format property of the field was set to "Rich Text".  Changing that to "Plain Text" (along with removing the Format property) allowed the field to be exported at its full length.

I think this is probably a bug, though Microsoft may say the behavior is "by design".  A workaround would be to automate Excel to create the spreadsheet, and populate the rows via CopyFromRecordset.

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

_{Thursday, February 22, 2018 3:40 PM ✅Answered}

Thanks for your help Dirk,

I had removed the @'s in the actual DB at one point, and then tried it with them again when removing them didnt resolve the problem.

They were there again when I created this trimmed copy of the DB.

Removing them again seems to have resolved the problem now, even with the text field being rich text.

Strange.

Nath

_{Monday, February 19, 2018 12:58 PM}

Export the query from the Access interface, and make sure that you tick the check box "Export data with formatting and layout".

When Access asks you whether you want to save the export steps, tick the check box.

Give the export steps a name, then click Save Export.

In your code, use

DoCmd.RunSavedImportExport "NameOfExportSteps"

substituting the name you used.

Regards, Hans Vogelaar (http://www.eileenslounge.com)

_{Monday, February 19, 2018 1:27 PM}

Hello Hans,

When I export the Query using the Access Interface and select the 'Export data with formatting and layout" The spreadsheet still does not contain more than 255 characters in the field.

Am I missing a default setting somewhere in Office that is making the spreadsheets columns 'General' and limiting them to 255 chars perhaps?

Thanks

Nathan

Nath

_{Monday, February 19, 2018 3:53 PM}

Hi Nath,

I think what you're missing is the "why" the memo field is being truncated. This could happen if your query is "grouping" on the memo field, for example. Can you post the SQL statement for your query? Thanks.

_{Tuesday, February 20, 2018 8:58 AM}

I have a query called 'SearchQuery' that does "SELECT .... fields... FROM ... table.... ORDER BY ... [id field]"

Then I have the 'Exporting' query that I am trying to export the results, which is using the 'SearchQuery'. It does " SELECT searchquery.fields..., and a 'Status' name from an adjoining table where the id is in the searchquery.status field FROM SearchQuery INNER JOIN Status ON SearchQuery.Status = Status.[id number] WHERE SearchQuery.Type = 1 AND ... ORDER BY SearchQuery.[id number] DESC;

Nath

_{Tuesday, February 20, 2018 3:41 PM}

Hi Nath,

And if you simply run/open the query, the memo field is not truncated? It only gets truncated when you export the query?

_{Tuesday, February 20, 2018 3:51 PM}

Hi,

Yeah, I have a record with a field that is over 300 characters long. When the query runs it sees all 300+ characters in the 'Description' field correctly. However, when exporting to excel the 'Description' field only shows 255 characters.

The Data Type for the 'Description' field is 'Long Text'

and it's properties are:

Format:@

Caption

Required: No

Allow Zero Length: yes

Indexed: No

Unicode Compression: No

IME Mode: No Control

IME Sentence Mode: None

Text Format: Rich text

Text Align: General

Append Only: No

Nath

_{Tuesday, February 20, 2018 4:01 PM}

Hi Nath,

Thanks for confirming the situation. However, I just tried creating a table with a memo field and then created a query against it. When I exported the query to Excel, I still got all the data from the memo field (way over 255 chars).

So, there must still be something about your setup we don't understand. Are you able to share it?

_{Tuesday, February 20, 2018 4:19 PM}

The Data Type for the 'Description' field is 'Long Text'

and it's properties are:

Format:@

Try clearing the Format property.

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

_{Tuesday, February 20, 2018 4:42 PM}

Hi Dirk,

Good catch! In my case, however, when I added the Format:@ to the table, both my table and query showed only 255 chars in the memo field, which is not the same as what Nath said earlier. He said the table and query show over 255 chars and only get truncated when exported to Excel.

Now, when I exported my newly formatted query to Excel, I still got over 255 chars even though the query only shows 255 chars.

Confusing...

_{Tuesday, February 20, 2018 5:38 PM}

Ah, but if the *query* specifies the "@" format for that field, then it will be truncated if you tell Access to export it "with formatting and layout".

So, Nath, check the query itself to see if, in the field grid, the properties of the Description field specify the Format property.  If they do, remove it.

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

_{Tuesday, February 20, 2018 5:53 PM}

Makes sense!

_{Wednesday, February 21, 2018 8:26 AM}

Hello,

I removed the @ symbol from the Format field for the Description field in the Table, and checked both of the queries to ensure there is no @ symbol in the Format field either.

I then tested exporting again and the spreadsheet still contains the field truncated to 255 characters.

I also tested with the format field containing @ in the table and the queries but get the same result.

Is there another setting that could be causing the truncating to 255 characters?

Thanks for you help with this. Much appreciated.

Nath

_{Wednesday, February 21, 2018 1:51 PM}

Could post your database, or a cut-down copy of it containing only the elements necessary to show the problem, on a file-sharing service like OneDrive or Dropbox, where we could download it to investigate? If so, please compact it before posting it, and zip it to reduce the file-size to be downloaded.

If you can't post it somewhere, you could e-mail it to me.  You can find my e-mail address on my website, which is listed in my signature and in my forum profile.

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

_{Wednesday, February 21, 2018 3:48 PM}

Hi Nath,

I know you tried to answer my previous question already but could you also please post the actual SQL statement of your query? Thanks.

_{Wednesday, February 21, 2018 3:56 PM}

Exporting Query:

SELECT SubmittedSearchQuery.[RFC No], SubmittedSearchQuery.AuthorityPriority, SubmittedSearchQuery.Title, SubmittedSearchQuery.Description, SubmittedSearchQuery.[Date In], Status.Status

FROM SubmittedSearchQuery INNER JOIN Status ON SubmittedSearchQuery.Status = Status.[Order Number]

WHERE (((SubmittedSearchQuery.Type)=1) AND ((SubmittedSearchQuery.Status)<16 And (SubmittedSearchQuery.Status)<>13))

ORDER BY SubmittedSearchQuery.[RFC No] DESC;

Searching Query:

SELECT ESubmitted.*

FROM ESubmitted

ORDER BY ESubmitted.ChangeID;

I shant be able to post the actual DBs or email them though, sorry Dirk.

Nath

_{Wednesday, February 21, 2018 4:00 PM}

Hi Nath,

Thanks! Couple of questions though:

1. Are ESubmitted and Status both tables?

2. Is SubmittedSearchQuery what you were referring to as the "Searching Query" above?

_{Wednesday, February 21, 2018 4:06 PM}

Hello,

I removed the @ symbol from the Format field for the Description field in the Table, and checked both of the queries to ensure there is no @ symbol in the Format field either.

I then tested exporting again and the spreadsheet still contains the field truncated to 255 characters.

I also tested with the format field containing @ in the table and the queries but get the same result.

Is there another setting that could be causing the truncating to 255 characters?

Thanks for you help with this. Much appreciated.

Nath

Do any of the first eight rows in the result have a column where the text exceeds 255 characters? If not can you change the column in question for one of the first eight so that it exceeds 255 characters and then try the export again?

If this works then you can probably change the TypeGuessRows Registry entries to 0 so that the Excel driver scans all of the rows, instead of the first eight, when determining column data types.

Paul ~~~~ Microsoft MVP (Visual Basic)

_{Thursday, February 22, 2018 7:14 AM}

Hi NaPazz,

>>I shant be able to post the actual DBs or email them though, sorry Dirk.

There is no need to share us the product DBs, you could create a simple test database which could reproduce your issue, and then share the test database with us.

We could not reproduce your issue based on your current query and descrption.

Best Regards,

Tao Zhou

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [email protected].

_{Thursday, February 22, 2018 2:17 PM}

Hello,

I have recreated the problem in a trimmed version of the database that I could share. How can I upload it to here?

Nath

_{Thursday, February 22, 2018 2:24 PM}

I don't think you can upload it directly to this forum, but you could upload it to a file-sharing service like OneDrive or DropBox, and post the link here.

If that fails, you could e-mail it to me, but then you'll only  get my eyes on it.

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

_{Thursday, February 22, 2018 2:48 PM}

Thanks,

I have uploaded the example DB to here: https://drive.google.com/file/d/1GfcydVBxIYIlm4KUUysTULNrrdinmfDz/view?usp=sharing

Run qryNormalChangesExporter,

Notice Description over 255 characters.

Export to Excel qryNormalChangesExporter,

Notice Description truncated to first 255 characters

Nath

_{Thursday, February 22, 2018 3:14 PM}

I downloaded and looked at it, and it's as I originally suspected, and you denied: the Format property of the Description field is set to "@" in both the table and the query.

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

_{Thursday, February 22, 2018 3:53 PM}

Hi Nath,

Glad to hear you finally got it sorted out. Cheers!

_{Thursday, February 22, 2018 8:01 PM}

I'm glad you got it working.  Strange that it's working for you now even with the field being set to Rich Text format;  that's still not working for me.

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

_{Friday, February 23, 2018 11:42 AM}

Dirk,

Yes, the Description field in the table is Rich Text.

The Description field in the query has the 'Text Format' property left blank. 

Do you have Rich Text for that 'Text Format' property? Perhaps it will work for you too if you make it blank.

Nathan

Nath

_{Friday, February 23, 2018 3:31 PM}

Yes, the Description field in the table is Rich Text.

The Description field in the query has the 'Text Format' property left blank. 

Do you have Rich Text for that 'Text Format' property? Perhaps it will work for you too if you make it blank.

I find that I can leave the Text Format property set to "Rich Text" in the table, so long as I override that in one of the queries, setting the Text Format property to "Plain Text" for that field.

Did you check both queries, "SubmittedSearchQuery" and "qryNormalChangesExporter", to see if along the way you overrode the table setting?

Incidentally, I'm testing with Access 2013, so if you're using a different version it's conceivable you could be getting different behavior due to the Access version.  I'd be surprised, but it could be.

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

_{Tuesday, July 28, 2020 7:45 PM}

I am finding that exporting a Table (set with long text fields formatted to plain text) also truncates when exported to Microsoft Excel (as of July 28, 2020). It's more than 255 characters, but it still truncates (like around 805 words when the cell contents are placed in Word to see how long it is).

I've tried everything I can think of (exporting in .CSV as an example) to get a full export, and just can't get it done.  Ultimately, I need the export in a .CSV or Excel format to upload into a WordPress site via TablePress (a nifty plug-in that makes table display wonderfully easy for large chunks of data).

The truncation is DEFINITELY happening between Access and Excel, as the data is missing before I upload it into TablePress.

Any updated ideas on how to fix this? I sopped bothering with a Query and just went to exporting my Table directly to avoid the other truncation issues. I HAVE discovered that sometimes, if a cell gets populated with a space at the end or some other invisibile character (that shouldn't happen in plain text mode, but it does anyway), that will cause a problem, too, but mostly that causes a problem when uploading the spreadsheet to TablePress (creating a column break or some other unexpected weirdness).

HELP!

_{Tuesday, July 28, 2020 8:20 PM}

Also just tested exports to a variety of TEXT and HTML formats, and those exports do NOT truncate. This seems to suggest that the conflict lies with the export process from Access to Excel.

_{Monday, September 14, 2020 2:49 PM}

I am dealing with the same issue. If the issue is resolved, please update.

_{Monday, September 14, 2020 3:41 PM}

I am dealing with the same issue. If the issue is resolved, please update.

What exactly is your situation?  Using A2016, I find that if I check the "Export data with formatting and layout" option when exporting manually, or if I use DoCmd.TransferSpreadsheet, I don't see truncation.  But if I check the option "Export data with formatting and layout", my Long Text field is truncated to 4000 characters,

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html