14,958
edits
Troubleshooting of common Excel errors: Difference between revisions
Jump to navigation
Jump to search
m
→Resolving Excel File Save as CSV Causing Garbled Text
| (2 intermediate revisions by the same user not shown) | |||
| Line 46: | Line 46: | ||
'''Solutions''' | '''Solutions''' | ||
There are three solution methods | |||
* Upload the Excel file to [https://workspace.google.com/intl/zh-TW/products/sheets/ Google Sheets], then download as CSV file. CSV files exported from Google Sheets will be UTF-8 encoded, avoiding garbled text issues. | * Upload the Excel file to [https://workspace.google.com/intl/zh-TW/products/sheets/ Google Sheets], then download as CSV file. CSV files exported from Google Sheets will be UTF-8 encoded, avoiding garbled text issues. | ||
* Using the Open-source software<ref>[https://en.wikipedia.org/wiki/Open-source_software Open-source software - Wikipedia]</ref> [https://zh-tw.libreoffice.org/ LibreOffice] to open the Excel file, then when saving the file, select "Text CSV (.csv)" format and choose the "Character set: Unicode (UTF-8)" option. | |||
* Using newer versions of Excel (such as Office 365), and selecting the "CSV UTF-8 (Comma delimited) (.csv)" option when saving. | * Using newer versions of Excel (such as Office 365), and selecting the "CSV UTF-8 (Comma delimited) (.csv)" option when saving. | ||
=== Resolving "Support 32767 characters in a cell only" Error When Exporting Database Data to Excel Files === | |||
'''Problem Description''' | |||
When exporting data from database to Excel files, the error message [ERR] Support 32767 characters in a cell only appears. | |||
'''Root Cause''' | |||
Excel cells have a maximum capacity of 32,767 characters <ref>[https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 Excel specifications and limits - Microsoft Support]</ref>. When a field in a database record contains content exceeding this limit, the [ERR] Support 32767 characters in a cell only error message occurs. | |||
'''Solution''' | |||
This can be resolved through the following two approaches <ref>[https://errerrors.blogspot.com/2023/07/fix-the-error-support-32767-characters-in-a-cell-only-on-navicat.html 解決 Navicat 匯出 Excel 檔案遇到 [ERR] Support 32767 characters in a cell only 錯誤訊息]</ref>: | |||
1. Use file formats that do not have the 32767 character limitation | |||
2. Use the SUBSTRING function to reduce content length | |||
MySQL Query That Causes the Error | |||
<pre> | |||
SELECT | |||
title, | |||
content | |||
FROM my_column | |||
</pre> | |||
Corrected MySQL Query | |||
<pre> | |||
SELECT | |||
title, | |||
SUBSTRING(content, 1, 32767) AS content | |||
FROM my_column | |||
</pre> | |||
== References == | == References == | ||