14,982
edits
Export MySQL query to Excel file: Difference between revisions
Jump to navigation
Jump to search
m
→Database management tools
(→Tools) |
|||
| (72 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 == | |||
=== One step conversion: ex MySQL query -> Excel file === | |||
* PHPExcel | Methods of exporting MySQL query -> XLS/XLSX file: PHP scripts or other MySQL client softwares e.g. | ||
* [https://phpexcel.codeplex.com/ PHPExcel]: PHP scripts | |||
* 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}} | |||
== | {{exclaim}} Notice the limit of Excel XLS file format: | ||
# "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 檔) | |||
#* validate the count of characters: MySQL {{kbd | key = <nowiki>CHAR_LENGTH</nowiki>}} or Excel {{kbd | key = <nowiki>LEN</nowiki>}} functions | |||
# "Length of formula contents: 8,192 characters" If the content start with the symbol {{kbd | key =<nowiki>=</nowiki>}} or {{kbd | key =<nowiki>-</nowiki>}}, Excel will consider the content is formula. | |||
#* Using CONCAT {{kbd | key =<nowiki>'</nowiki>}} symbol ex: {{kbd | key = <nowiki>CONCAT('\'', string_column)</nowiki>}} If the content is not formula. | |||
# "Worksheet size: 1,048,576 rows": | |||
#* Using {{kbd | key =LIMIT 1048576}} for EXCEL 2007 | |||
integrated solution for Excel limit 1 ~ limit 3 | |||
* {{kbd | key =<nowiki>SELECT SUBSTRING(IF(LEFT(string_column, 1) = '-' OR LEFT(string_column, 1) = '=', CONCAT('\'', string_column), string_column), 1, 32767) FROM <table> LIMIT 1048576</nowiki>}} | |||
相關文章 | |||
* [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=== | |||
==== Step 1: Export the MySQL query -> CSV file ==== | |||
Approaches | |||
* [[Database management tools]] (MySQL client softwares) | |||
* PHPExcel | |||
* [http://dev.mysql.com/doc/refman/5.7/en/mysqldump-delimited-text.html MySQL :: MySQL 5.7 Reference Manual :: 8.4.3 Dumping Data in Delimited-Text Format with mysqldump] | |||
** Required {{kbd | key = FILE}} permission ex: {{kbd | key = GRANT FILE ON *.* TO 'user_name'@'localhost';}} If you met the following error message: | |||
<pre> | |||
mysqldump: Got error: 1045: Access denied for user 'user_name'@'localhost' (using password: YES) when executing 'SELECT INTO OUTFILE' | |||
</pre> | |||
Condition | |||
* Condition 1: If the rows count of MySQL query is larger than 65,535 rows. You can use MS Excel. | |||
* Condition 2: If the rows count of MySQL query is less than 65,535 rows. You can use [https://zh-tw.libreoffice.org/discover/calc/ LibreOffice Calc] & MS Excel. | |||
* Condition 3: Replace the double quote with double double quote if the enclosure character is double quote. | |||
* Condition 4: (optional) Replace the return character with space character or other character e.g. {{kbd | key = ↵}} ([https://www.compart.com/en/unicode/U+21B5 U+21B5]). SQL query syntax: | |||
<pre> | |||
REPLACE(REPLACE(REPLACE(column_name, '\r\n', ''), '\n', ''), '\r', '') AS column_name | |||
</pre> | |||
Validation: Use the editor supported [[Regular expression]], | |||
* Search {{kbd | key=<nowiki>^[^"]</nowiki>}} in the csv files to search the rows which contains return symbols. The {{kbd | key=<nowiki>"</nowiki>}} symbol is the "text qualifier" what I specified before export the MySQL query. | |||
==== Step 2: Convert CSV to XLSX/XLS file format. ==== | |||
* 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://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]) | |||
==== Step 3: Data verification ==== | |||
# Verify the data rows count: Equal to the rows count of MySQL query. '''Not''' equal to 65,535 rows or less than the rows count of MySQL query. | |||
# Verify the data column is in correct position | |||
#* {{exclaim}} If the CSV is not well-formatted, sometimes the data column after imported to Excel is in wrong position. | |||
# Verify the rows count | |||
#* If the data size is less than 65,535 rows, convert CSV to Excel/XLS by [https://zh-tw.libreoffice.org/discover/calc/ LibreOffice Calc] is recommended. | |||
#* If the data size is larger than 65,535 rows, Adjust the MySQL query to avoid the column value contains the | |||
#** enclosure character e.g. {{kbd | key=Tab}} key | |||
#** escape the enclosure character e.g. {{kbd | key=<nowiki>"</nowiki>}} become {{kbd | key=<nowiki>""</nowiki>}} | |||
#** return symbol to separate different data row. | |||
== Troubleshooting of technical issues == | |||
=== If it costs too much time to convert to Excel file === | |||
The important steps before generating CSV file. These steps reduce the problems when the CSV file was converted to Excel file. | |||
* Add '''delimiter character''' between different column ex: comma symbol (,) or tab symbol | * Add '''delimiter character''' between different column ex: comma symbol (,) or tab symbol | ||
* Add '''enclosure character''' ex: double quote symbol (") or single quote symbol (') | * Add '''enclosure character''' ex: double quote symbol (") or single quote symbol (') | ||
* Add '''escape character''' escape the column value if contains the '''enclosure character''' | * Add '''escape character''' escape the column value if contains the '''enclosure character''' | ||
* Remove return symbol<ref>[http://stackoverflow.com/questions/1504962/how-to-remove-new-line-characters-from-data-rows-in-mysql trim - How to remove new line characters from data rows in mysql? - Stack Overflow]</ref>: <pre>SELECT REPLACE(REPLACE(REPLACE(`my_column`, '\r\n', ' '), '\n', ' '), '\r', ' ') </pre> | * Remove return symbol<ref>[http://stackoverflow.com/questions/1504962/how-to-remove-new-line-characters-from-data-rows-in-mysql trim - How to remove new line characters from data rows in mysql? - Stack Overflow]</ref>: <pre>SELECT REPLACE(REPLACE(REPLACE(`my_column`, '\r\n', ' '), '\n', ' '), '\r', ' ') </pre> | ||
=== 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> | |||
* '''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]] | |||
| Line 21: | Line 86: | ||
<references/> | <references/> | ||
[[Category:Data Science]] [[Category:Excel]] | {{Template:Data factory flow}} | ||
[[Category:Data Science]] [[Category:Excel]] [[Category:MySQL]] | |||