Troubleshooting of common Excel errors - Revision history

Planetoid: /* Resolving Excel File Save as CSV Causing Garbled Text */

2025-07-14T07:10:10Z

Resolving Excel File Save as CSV Causing Garbled Text

← Older revision		Revision as of 15:10, 14 July 2025
Line 48:		Line 48:
	There are three solution methods		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 [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 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.

Planetoid: /* Resolving Excel File Save as CSV Causing Garbled Text */

2025-07-14T07:05:32Z

Resolving Excel File Save as CSV Causing Garbled Text

← Older revision		Revision as of 15:05, 14 July 2025
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 [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 ===		=== Resolving "Support 32767 characters in a cell only" Error When Exporting Database Data to Excel Files ===

Planetoid: /* Resolving Excel File Save as CSV Causing Garbled Text */

2025-07-14T07:00:12Z

Resolving Excel File Save as CSV Causing Garbled Text

@@ Line 48: / Line 48: @@
 * 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 newer versions of Excel (such as Office 365), and selecting the "CSV UTF-8 (Comma delimited) (.csv)" option when saving.
 == References ==

Planetoid: /* Resolving Excel File Save as CSV Causing Garbled Text */

2025-07-14T06:16:30Z

Resolving Excel File Save as CSV Causing Garbled Text

← Older revision		Revision as of 14:16, 14 July 2025
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'''

	* 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.
	* ~~Older versions of Excel only provide general CSV options when saving (defaulting to Big5 encoding), which is the main cause of garbled text. Recommend 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.


	== References ==		== References ==

Planetoid at 06:05, 14 July 2025

2025-07-14T06:05:42Z

← Older revision		Revision as of 14:05, 14 July 2025
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 ===

Planetoid: 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..."

2025-07-14T06:05:02Z

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..."

New page

== Troubleshooting of Excel errors ==

{{LanguageSwitcher | content = [[Troubleshooting of 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.

'''Solutions'''

* 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.
* Older versions of Excel only provide general CSV options when saving (defaulting to Big5 encoding), which is the main cause of garbled text. Recommend using newer versions of Excel (such as Office 365), and selecting the "CSV UTF-8 (Comma delimited) (.csv)" option when saving.

== References ==
<references />

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