Export MySQL query to Excel file - Revision history

Planetoid: /* Database management tools */

2026-04-14T10:48:29Z

Database management tools

← Older revision		Revision as of 18:48, 14 April 2026
Line 78:		Line 78:
	* '''Number''' data types in result set became '''string''' after exported as XLSX file <ref>[https://exceljet.net/formula/convert-text-to-numbers Excel formula: Convert text to numbers \| Exceljet]</ref>		* '''Number''' data types in result set became '''string''' after exported as XLSX file <ref>[https://exceljet.net/formula/convert-text-to-numbers Excel formula: Convert text to numbers \| Exceljet]</ref>
	* '''Date time''' data types in result set became '''string''' after exported as XLSX file<ref>[https://errerrors.blogspot.com/2016/12/excel-value-text.html Excel 如何轉換文字格式的日期時間格式 (使用 VALUE, TEXT 函數)]</ref>		* '''Date time''' data types in result set became '''string''' after exported as XLSX file<ref>[https://errerrors.blogspot.com/2016/12/excel-value-text.html Excel 如何轉換文字格式的日期時間格式 (使用 VALUE, TEXT 函數)]</ref>

	~~== Database management tools ==~~
	~~[[Database management tools]]~~

	== Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits ==		== Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits ==

Planetoid: /* One step conversion: ex MySQL query -> Excel file */

2026-04-14T10:48:16Z

One step conversion: ex MySQL query -> Excel file

← Older revision		Revision as of 18:48, 14 April 2026
Line 6:		Line 6:
	Methods of exporting MySQL query -> XLS/XLSX file: PHP scripts or other MySQL client softwares e.g.		Methods of exporting MySQL query -> XLS/XLSX file: PHP scripts or other MySQL client softwares e.g.
	* [https://phpexcel.codeplex.com/ PHPExcel]: PHP scripts		* [https://phpexcel.codeplex.com/ PHPExcel]: PHP scripts
	* [https://www.quest.com/products/toad-for-mysql/ Toad for MySQL] for {{Win}}		* Using [[Database management tools]] e.g. [https://www.quest.com/products/toad-for-mysql/ Toad for MySQL] for {{Win}}, ''$'' [https://www.navicat.com/cht Navicat GUI] for {{Win}} & {{Mac}}
	* [https://www.spectralcore.com/fullconvert Full Convert Enterprise]: allows easy copying of tables and data between 40 or so supported databases. It will create all the tables, ~~copy all of your data, then create indexes and foreign keys. 30 days trial~~
	* ''$'' [https://www.navicat.com/cht Navicat GUI] for {{Win}} & {{Mac}}

	Notice the limit of Excel XLS file format:		{{exclaim}} Notice the limit of Excel XLS file format:
	# "Total number of characters that a cell can contain: 32,767 characters"		# "Total number of characters that a cell can contain: 32,767 characters"
	#* Using [http://www.w3resource.com/mysql/string-functions/mysql-substring-function.php MySQL SUBSTRING() function] ex: {{kbd \| key = <nowiki>SUBSTRING(string_column, 1, 32767)</nowiki>}} for EXCEL 97<ref>[https://support.microsoft.com/en-us/kb/296053 Summary of capability limitations in Excel 97]</ref>-2007<ref>[https://support.office.com/en-us/article/Excel-specifications-and-limits-16c69c74-3d6a-4aaf-ba35-e6eb276e8eaa?CorrelationId=1de7e8d2-91e2-4eec-8868-4dc8cbd23463&ui=en-US&rs=en-US&ad=US Excel specifications and limits]</ref> (XLS 檔)		#* Using [http://www.w3resource.com/mysql/string-functions/mysql-substring-function.php MySQL SUBSTRING() function] ex: {{kbd \| key = <nowiki>SUBSTRING(string_column, 1, 32767)</nowiki>}} for EXCEL 97<ref>[https://support.microsoft.com/en-us/kb/296053 Summary of capability limitations in Excel 97]</ref>-2007<ref>[https://support.office.com/en-us/article/Excel-specifications-and-limits-16c69c74-3d6a-4aaf-ba35-e6eb276e8eaa?CorrelationId=1de7e8d2-91e2-4eec-8868-4dc8cbd23463&ui=en-US&rs=en-US&ad=US Excel specifications and limits]</ref> (XLS 檔)

Planetoid at 03:32, 15 July 2025

2025-07-15T03:32:29Z

← Older revision		Revision as of 11:32, 15 July 2025
Line 90:		Line 90:
	== References ==		== References ==
	<references/>		<references/>

			{{Template:Data factory flow}}

	[[Category:Data Science]] [[Category:Excel]] [[Category:MySQL]]		[[Category:Data Science]] [[Category:Excel]] [[Category:MySQL]]

Unknown user at 06:51, 15 November 2021

2021-11-15T06:51:38Z

← Older revision		Revision as of 14:51, 15 November 2021
Line 1:		Line 1:
	Export MySQL query to Excel file (XLS or XLSX file)		Export MySQL query to Excel file (XLS or XLSX file)

	~~== Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits ==~~
	~~[[Microsoft Excel#Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits]]~~

	== Methods ==		== Methods ==
Line 85:		Line 83:
	== Database management tools ==		== Database management tools ==
	[[Database management tools]]		[[Database management tools]]

			== Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits ==
			[[Microsoft Excel#Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits]]


	== References ==		== References ==

Unknown user: /* Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits */

2021-11-15T06:51:13Z

Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits

@@ Line 2: / Line 2: @@
 == Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits ==
-不同試算表方案的工作表或儲存格的大小限制
+[[Microsoft Excel#Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits]]
 == Methods ==

Planetoid: Text replacement - "http://errerrors.blogspot.com" to "https://errerrors.blogspot.com"

2019-01-19T06:00:46Z

Text replacement - "http://errerrors.blogspot.com" to "https://errerrors.blogspot.com"

← Older revision		Revision as of 14:00, 19 January 2019
Line 15:		Line 15:
	<td>XLSX</td>		<td>XLSX</td>
	<td>Maximum of worksheet size is 1,048,576 rows by 16,384 columns.<ref>[https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3?ui=en-US&rs=en-US&ad=US&fromAR=1 Excel specifications and limits]</ref><ref>[http://office.microsoft.com/zh-tw/excel-help/HP010073849.aspx Excel 的規格及限制] 工作表大小</ref><ref>[http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HA103980614.aspx Excel specifications and limits - Excel]</ref> </td>		<td>Maximum of worksheet size is 1,048,576 rows by 16,384 columns.<ref>[https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3?ui=en-US&rs=en-US&ad=US&fromAR=1 Excel specifications and limits]</ref><ref>[http://office.microsoft.com/zh-tw/excel-help/HP010073849.aspx Excel 的規格及限制] 工作表大小</ref><ref>[http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HA103980614.aspx Excel specifications and limits - Excel]</ref> </td>
	<td>(1) Length of formula contents: 8,192 characters <br />(2) Total number of characters that a cell can contain: 32,767 characters<ref>[~~http~~://errerrors.blogspot.com/2016/02/navicat.html Err: 解決 Navicat 「存放裝置空間不足，無法完成此操作」問題訊息]</ref></td>		<td>(1) Length of formula contents: 8,192 characters <br />(2) Total number of characters that a cell can contain: 32,767 characters<ref>[https://errerrors.blogspot.com/2016/02/navicat.html Err: 解決 Navicat 「存放裝置空間不足，無法完成此操作」問題訊息]</ref></td>
	<td>Microsoft Excel 2007, EXCEL 2013 and [https://fileinfo.com/extension/xlsx more]</td>		<td>Microsoft Excel 2007, EXCEL 2013 and [https://fileinfo.com/extension/xlsx more]</td>
	</tr>		</tr>
Line 72:		Line 72:

	相關文章		相關文章
	* [~~http~~://errerrors.blogspot.com/2015/07/excel.html Err: 解決從資料庫輸出 Excel 檔案，遇到非常大數字的科學符號和有效位數的問題]		* [https://errerrors.blogspot.com/2015/07/excel.html Err: 解決從資料庫輸出 Excel 檔案，遇到非常大數字的科學符號和有效位數的問題]
	* [~~http~~://errerrors.blogspot.com/2016/02/navicat.html Err: 解決 Navicat 「存放裝置空間不足，無法完成此操作」問題訊息]		* [https://errerrors.blogspot.com/2016/02/navicat.html Err: 解決 Navicat 「存放裝置空間不足，無法完成此操作」問題訊息]

	=== Two steps conversion ex: MySQL query -> CSV file -> Excel file===		=== Two steps conversion ex: MySQL query -> CSV file -> Excel file===
Line 103:		Line 103:
	* MS Excel		* MS Excel
	** [https://www.itg.ias.edu/content/how-import-csv-file-uses-utf-8-character-encoding-0 How to import a .csv file that uses UTF-8 character encoding \| Information Technology Group]		** [https://www.itg.ias.edu/content/how-import-csv-file-uses-utf-8-character-encoding-0 How to import a .csv file that uses UTF-8 character encoding \| Information Technology Group]
	** [~~http~~://errerrors.blogspot.com/2010/10/csv-microsoft-excel-2007-openofficeorg.html Err: 解決CSV檔亂碼 (Microsoft Excel 2007 或 OpenOffice.org 3.2)] {{Win}}		** [https://errerrors.blogspot.com/2010/10/csv-microsoft-excel-2007-openofficeorg.html Err: 解決CSV檔亂碼 (Microsoft Excel 2007 或 OpenOffice.org 3.2)] {{Win}}
	** [~~http~~://errerrors.blogspot.com/2014/05/mac-office-excel-csv-microsoft-excel.html Err: Mac Office Excel 匯入中文 CSV 檔案 (Microsoft Excel for Mac 2011)] {{Mac}}		** [https://errerrors.blogspot.com/2014/05/mac-office-excel-csv-microsoft-excel.html Err: Mac Office Excel 匯入中文 CSV 檔案 (Microsoft Excel for Mac 2011)] {{Mac}}
	* [https://zh-tw.libreoffice.org/discover/calc/ LibreOffice Calc] ([http://portableapps.com/apps/office/libreoffice_portable LibreOffice Portable])		* [https://zh-tw.libreoffice.org/discover/calc/ LibreOffice Calc] ([http://portableapps.com/apps/office/libreoffice_portable LibreOffice Portable])

Planetoid: Text replacement - "errerrors.blogspot.tw" to "errerrors.blogspot.com"

2019-01-19T05:41:01Z

Text replacement - "errerrors.blogspot.tw" to "errerrors.blogspot.com"

← Older revision		Revision as of 13:41, 19 January 2019
Line 128:		Line 128:
	=== Data format in result set became string after exported as Excel file===		=== Data format in result set became string after exported as Excel file===
	* '''Number''' data types in result set became '''string''' after exported as XLSX file <ref>[https://exceljet.net/formula/convert-text-to-numbers Excel formula: Convert text to numbers \| Exceljet]</ref>		* '''Number''' data types in result set became '''string''' after exported as XLSX file <ref>[https://exceljet.net/formula/convert-text-to-numbers Excel formula: Convert text to numbers \| Exceljet]</ref>
	* '''Date time''' data types in result set became '''string''' after exported as XLSX file<ref>[https://errerrors.blogspot.tw/2016/12/excel-value-text.html Excel 如何轉換文字格式的日期時間格式 (使用 VALUE, TEXT 函數)]</ref>		* '''Date time''' data types in result set became '''string''' after exported as XLSX file<ref>[https://errerrors.blogspot.com/2016/12/excel-value-text.html Excel 如何轉換文字格式的日期時間格式 (使用 VALUE, TEXT 函數)]</ref>

	== Database management tools ==		== Database management tools ==

Planetoid: /* One step conversion: ex MySQL query -> Excel file */

2018-09-16T18:54:30Z

One step conversion: ex MySQL query -> Excel file

← Older revision		Revision as of 02:54, 17 September 2018
Line 54:		Line 54:
	=== One step conversion: ex MySQL query -> Excel file ===		=== One step conversion: ex MySQL query -> Excel file ===
	Methods of exporting MySQL query -> XLS/XLSX file: PHP scripts or other MySQL client softwares e.g.		Methods of exporting MySQL query -> XLS/XLSX file: PHP scripts or other MySQL client softwares e.g.
	* [https://www.spectralcore.com/fullconvert Full Convert Enterprise]: allows easy copying of tables and data between 40 or so supported databases. It will create all the tables, copy all of your data, then create indexes and foreign keys.
	* [https://phpexcel.codeplex.com/ PHPExcel]: PHP scripts		* [https://phpexcel.codeplex.com/ PHPExcel]: PHP scripts
	* [https://www.quest.com/products/toad-for-mysql/ Toad for MySQL] for {{Win}}		* [https://www.quest.com/products/toad-for-mysql/ Toad for MySQL] for {{Win}}
			* [https://www.spectralcore.com/fullconvert Full Convert Enterprise]: allows easy copying of tables and data between 40 or so supported databases. It will create all the tables, copy all of your data, then create indexes and foreign keys. 30 days trial
	* ''$'' [https://www.navicat.com/cht Navicat GUI] for {{Win}} & {{Mac}}		* ''$'' [https://www.navicat.com/cht Navicat GUI] for {{Win}} & {{Mac}}

172.68.154.54: /* One step conversion: ex MySQL query -> Excel file */

2018-09-16T17:36:41Z

One step conversion: ex MySQL query -> Excel file

← Older revision		Revision as of 01:36, 17 September 2018
Line 54:		Line 54:
	=== One step conversion: ex MySQL query -> Excel file ===		=== One step conversion: ex MySQL query -> Excel file ===
	Methods of exporting MySQL query -> XLS/XLSX file: PHP scripts or other MySQL client softwares e.g.		Methods of exporting MySQL query -> XLS/XLSX file: PHP scripts or other MySQL client softwares e.g.
	* [https://www.spectralcore.com/fullconvert Full Convert Enterprise		* [https://www.spectralcore.com/fullconvert Full Convert Enterprise]: allows easy copying of tables and data between 40 or so supported databases. It will create all the tables, copy all of your data, then create indexes and foreign keys.
	]: allows easy copying of tables and data between 40 or so supported databases. It will create all the tables, copy all of your data, then create indexes and foreign keys.
	* [https://phpexcel.codeplex.com/ PHPExcel]: PHP scripts		* [https://phpexcel.codeplex.com/ PHPExcel]: PHP scripts
	* [https://www.quest.com/products/toad-for-mysql/ Toad for MySQL] for {{Win}}		* [https://www.quest.com/products/toad-for-mysql/ Toad for MySQL] for {{Win}}

172.68.154.54: /* One step conversion: ex MySQL query -> Excel file */

2018-09-16T17:35:52Z

One step conversion: ex MySQL query -> Excel file

← Older revision		Revision as of 01:35, 17 September 2018
Line 54:		Line 54:
	=== One step conversion: ex MySQL query -> Excel file ===		=== One step conversion: ex MySQL query -> Excel file ===
	Methods of exporting MySQL query -> XLS/XLSX file: PHP scripts or other MySQL client softwares e.g.		Methods of exporting MySQL query -> XLS/XLSX file: PHP scripts or other MySQL client softwares e.g.
	* [https://www.spectralcore.com/fullconvert]: allows easy copying of tables and data between 40 or so supported databases. It will create all the tables, copy all of your data, then create indexes and foreign keys.		* [https://www.spectralcore.com/fullconvert Full Convert Enterprise
			]: allows easy copying of tables and data between 40 or so supported databases. It will create all the tables, copy all of your data, then create indexes and foreign keys.
	* [https://phpexcel.codeplex.com/ PHPExcel]: PHP scripts		* [https://phpexcel.codeplex.com/ PHPExcel]: PHP scripts
	* [https://www.quest.com/products/toad-for-mysql/ Toad for MySQL] for {{Win}}		* [https://www.quest.com/products/toad-for-mysql/ Toad for MySQL] for {{Win}}