Export MySQL query to Excel file: Difference between revisions
Jump to navigation
Jump to search
(→Tools) |
|||
| Line 2: | Line 2: | ||
== Tools == | == Tools == | ||
one step conversion: ex MySQL query -> XLS/XLSX file | === one step conversion: ex MySQL query -> Excel file === | ||
ex MySQL query -> XLS/XLSX file | |||
* [https://phpexcel.codeplex.com/ PHPExcel] | * [https://phpexcel.codeplex.com/ PHPExcel] | ||
* other MySQL client softwares | * other MySQL client softwares | ||
two steps conversion: ex: (1) MySQL query -> CSV/XLS file (2) CSV --> XLSX or XLS --> XLSX | === two steps conversion ex: MySQL query -> CSV file -> Excel file=== | ||
* PHPExcel or MySQL client softwares | ex: (1) MySQL query -> CSV/XLS file (2) CSV --> XLSX or XLS --> XLSX. | ||
* | for step (1) MySQL query -> CSV/XLS file | ||
* 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] | |||
for step (2) CSV --> XLSX or XLS --> XLSX. | |||
* MS Excel | |||
* [https://zh-tw.libreoffice.org/discover/calc/ LibreOffice Calc] | |||
== 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 == | ||
Revision as of 11:15, 12 April 2016
Export MySQL query to Excel file (XLS or XLSX file)
Tools
one step conversion: ex MySQL query -> Excel file
ex MySQL query -> XLS/XLSX file
- PHPExcel
- other MySQL client softwares
two steps conversion ex: MySQL query -> CSV file -> Excel file
ex: (1) MySQL query -> CSV/XLS file (2) CSV --> XLSX or XLS --> XLSX. for step (1) MySQL query -> CSV/XLS file
- PHPExcel or MySQL client softwares
- MySQL :: MySQL 5.7 Reference Manual :: 8.4.3 Dumping Data in Delimited-Text Format with mysqldump
for step (2) CSV --> XLSX or XLS --> XLSX.
- MS Excel
- LibreOffice Calc
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 enclosure character ex: double quote symbol (") or single quote symbol (')
- Add escape character escape the column value if contains the enclosure character
- Remove return symbol[1]:
SELECT REPLACE(REPLACE(REPLACE(`my_column`, '\r\n', ' '), '\n', ' '), '\r', ' ')