Export MySQL query to Excel file

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: 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 : 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:

  SELECT REPLACE(REPLACE(REPLACE(`my_column`, '\r\n', '   '), '\n', '   '), '\r', '   ')