15,076
edits
Export MySQL query to Excel file: Difference between revisions
Jump to navigation
Jump to search
m
→Database management tools
| (8 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
Export MySQL query to Excel file (XLS or XLSX file) | Export MySQL query to Excel file (XLS or XLSX file) | ||
== Methods == | == Methods == | ||
=== 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://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}} | ||
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 檔) | ||
| Line 73: | Line 21: | ||
相關文章 | 相關文章 | ||
* [ | * [https://errerrors.blogspot.com/2015/07/excel.html Err: 解決從資料庫輸出 Excel 檔案，遇到非常大數字的科學符號和有效位數的問題] | ||
* [ | * [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 104: | Line 52: | ||
* 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] | ||
** [ | ** [https://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/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]) | ||
| Line 129: | Line 77: | ||
=== 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. | * '''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> | ||
== Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits == | |||
[[Microsoft Excel#Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits]] | |||
== References == | == References == | ||
<references/> | <references/> | ||
{{Template:Data factory flow}} | |||
[[Category:Data Science]] [[Category:Excel]] [[Category:MySQL]] | [[Category:Data Science]] [[Category:Excel]] [[Category:MySQL]] | ||