14,970
edits
Export MySQL query to Excel file: Difference between revisions
Jump to navigation
Jump to search
→two steps conversion ex: MySQL query -> CSV file -> Excel file
| Line 11: | Line 11: | ||
* XLSX file format <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>: Maximum of worksheet size is 1,048,576 rows by 16,384 columns. | * XLSX file format <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>: Maximum of worksheet size is 1,048,576 rows by 16,384 columns. | ||
=== | === Two steps conversion ex: MySQL query -> CSV file -> Excel file=== | ||
Step 1: Export the MySQL query -> CSV/XLS file | |||
* PHPExcel or MySQL client softwares | * PHPExcel or MySQL client softwares | ||
* [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] | * [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 permission ex: {{kbd | key = GRANT FILE ON *.* TO 'user_name'@'localhost';}} If you met the following error message: | ** Required {{kbd | key = FILE}} permission ex: {{kbd | key = GRANT FILE ON *.* TO 'user_name'@'localhost';}} If you met the following error message: | ||
<pre> | <pre> | ||
mysqldump: Got error: 1045: Access denied for user 'user_name'@'localhost' (using password: YES) when executing 'SELECT INTO OUTFILE' | mysqldump: Got error: 1045: Access denied for user 'user_name'@'localhost' (using password: YES) when executing 'SELECT INTO OUTFILE' | ||
</pre> | </pre> | ||
Step 2: Import CSV to XLSX or XLS file format. | |||
* 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] | |||
** [http://errerrors.blogspot.tw/2010/10/csv-microsoft-excel-2007-openofficeorg.html Err: 解決CSV檔亂碼 (Microsoft Excel 2007 或 OpenOffice.org 3.2)] {{Win}} | |||
** [http://errerrors.blogspot.tw/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] | * [https://zh-tw.libreoffice.org/discover/calc/ LibreOffice Calc] | ||
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}} Because the CSV is not well-formatted, sometimes the data column after imported to Excel is in wrong position. If the data size is less than 65,535 rows, convert CSV to Excel 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 and return symbol to separate different data row. | |||
== Technical issues: If it costs too much time to convert to Excel file == | == Technical issues: If it costs too much time to convert to Excel file == | ||