Editing Troubleshooting of common Excel errors


== Troubleshooting of Excel errors ==

{{LanguageSwitcher | content = [[Troubleshooting of common Excel errors | EN]], [[Troubleshooting of common Excel errors in Mandarin | 漢字]] }}

=== 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 than performing data processing or calculations directly in Excel.
** When deleting approximately 10,000 rows that meet specific criteria is too slow, filter for rows that meet different criteria, then copy and paste to a new worksheet - this will be faster. If direct copy-paste also takes more than 5 minutes, choose to paste values only. Related article: [https://errerrors.blogspot.com/2020/03/resolve-too-slow-to-delete-data-rows-in-excel.html Resolve too slow to delete data rows in Excel]
* Reducing data processing complexity
** Select all worksheet cell data, copy, then paste special values to a blank worksheet. Removing formulas will speed up operations on the new worksheet. {{exclaim}} Note that time format values may be corrupted and become long strings of numbers, requiring additional cell formatting to time format.
** Multiple filter criteria consume more system resources. Instead of using filter criteria, using functions for processing will be faster.
* Computer performance
** Upgrading software from 32-bit to 64-bit version enables more efficient memory usage. e.g. [https://support.office.com/en-us/article/Choose-between-the-64-bit-or-32-bit-version-of-Office-2dee7807-8f95-4d0c-b5fe-6c6f49b8d261 Choose between the 64-bit or 32-bit version of Office - Office Support]
** Close unused Excel files to conserve computer performance
* Switching to different software
** First use [https://zh-tw.libreoffice.org/discover/calc/ LibreOffice Calc] to operate ODS ([https://en.wikipedia.org/wiki/OpenDocument OpenDocument] Spreadsheet) or Excel XML file formats, which process faster. Then export to Excel file format.

=== Excel CSV Import Field Misalignment ===
When Excel opens CSV files, field misalignment occurs

Cause:
* If CSV file field values contain line break symbols (Return symbol), Excel will display errors when opening: what should be the same field gets split across lines and becomes a second data record.

Solution:
* Use [https://zh-tw.libreoffice.org/ LibreOffice] to open the CSV file, then convert to Excel format. Recommend opening the file with Office Excel again to confirm there are no issues.

=== This text file contains more data than can fit in a single worksheet ===
When importing CSV files into Excel, encountering "This text file contains more data than can fit in a single worksheet."

Cause:
* CSV file data records exceed the maximum number of records that a single worksheet can accommodate: 1,048,576 (rows).<ref>[https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 Excel specifications and limits - Excel]</ref>

Solution:
* Limit the number of records

=== Resolving Excel File Save as CSV Causing Garbled Text ===
'''Problem'''

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'''

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.
* 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 />

[[Category: Software]]
[[Category: Spreadsheet]] 
[[Category: Excel]]  
[[Category: Data Science]]