Export MySQL query to Excel file: Difference between revisions
Jump to navigation
Jump to search
| Line 2: | Line 2: | ||
== Tools == | == Tools == | ||
=== | === One step conversion: ex MySQL query -> Excel file === | ||
Methods of export MySQL query -> XLS/XLSX file | |||
* [https://phpexcel.codeplex.com/ PHPExcel] | * [https://phpexcel.codeplex.com/ PHPExcel] | ||
* other MySQL client softwares | * other MySQL client softwares | ||
Notice the limit of Excel XLS file format: | |||
* XLS file format<ref>[http://superuser.com/questions/366468/what-is-the-maximum-allowed-rows-in-a-microsoft-excel-xls-or-xlsx What is the maximum allowed rows in a Microsoft Excel .xls or .xlsx - Super User]</ref><ref>[https://support.office.com/en-us/article/Worksheet-compatibility-issues-f9c80c5b-5afc-40da-a841-b888746abd40 Worksheet compatibility issues - Excel]</ref>: Maximum of worksheet size is 65,536 rows by 256 columns. First row is column name. {{exclaim}} Actual data size is 65,535 rows. | |||
* 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=== | === two steps conversion ex: MySQL query -> CSV file -> Excel file=== | ||
Revision as of 11:33, 28 September 2016
Export MySQL query to Excel file (XLS or XLSX file)
Tools
One step conversion: ex MySQL query -> Excel file
Methods of export MySQL query -> XLS/XLSX file
- PHPExcel
- other MySQL client softwares
Notice the limit of Excel XLS file format:
- XLS file format[1][2]: Maximum of worksheet size is 65,536 rows by 256 columns. First row is column name.
Actual data size is 65,535 rows. - XLSX file format [3]: 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) 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
- Required permission ex: GRANT FILE ON *.* TO 'user_name'@'localhost'; If you met the following error message:
mysqldump: Got error: 1045: Access denied for user 'user_name'@'localhost' (using password: YES) when executing 'SELECT INTO OUTFILE'
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[4]:
SELECT REPLACE(REPLACE(REPLACE(`my_column`, '\r\n', ' '), '\n', ' '), '\r', ' ')