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 28: | Line 28: | ||
=== Two steps conversion ex: MySQL query -> CSV file -> Excel file=== | === Two steps conversion ex: MySQL query -> CSV file -> Excel file=== | ||
Step 1: Export the MySQL query -> CSV file | ==== Step 1: Export the MySQL query -> CSV 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] | ||
| Line 36: | Line 36: | ||
</pre> | </pre> | ||
Step 2: Import CSV to XLSX/XLS file format. | Condition 1: If the rows count of MySQL query is larger than 65,535 rows. You can use MS Excel. | ||
* Return symbol of CSV file should be replaced. | |||
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. | |||
==== Step 2: Import CSV to XLSX/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] | ** [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}} | ** [http://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}} | ** [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://zh-tw.libreoffice.org/discover/calc/ LibreOffice Calc] | ||
Step 3: Data verification | ==== 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 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 | # Verify the data column is in correct position | ||