15,063
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
(Created page with " == Troubleshooting of Excel errors == {{LanguageSwitcher | content = EN, 漢字 }} === Excel Performance Issues === * Alternative solutions when dealing with large datasets ** When Excel data contains approximately one million records, operations may take several hours. Consider using a database instead. Process data in the database first, then export to Excel format, rather...") |
|||
| (4 intermediate revisions by the same user not shown) | |||
| Line 2: | Line 2: | ||
== Troubleshooting of Excel errors == | == Troubleshooting of Excel errors == | ||
{{LanguageSwitcher | content = [[Troubleshooting of Excel errors | EN]], [[Troubleshooting of common Excel errors in Mandarin | 漢字]] }} | {{LanguageSwitcher | content = [[Troubleshooting of common Excel errors | EN]], [[Troubleshooting of common Excel errors in Mandarin | 漢字]] }} | ||
=== Excel Performance Issues === | === Excel Performance Issues === | ||
| Line 39: | Line 39: | ||
When saving Excel files containing text in other languages as CSV files, the content becomes garbled. | When saving Excel files containing text in other languages as CSV files, the content becomes garbled. | ||
'''Root cause''' | |||
Older versions of Excel only provide general CSV options when saving (defaulting to Big5 encoding), which is the main cause of garbled text. | |||
'''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. | |||
=== 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 == | ||