Export MySQL query to Excel file: Difference between revisions
Jump to navigation
Jump to search
| Line 11: | Line 11: | ||
== 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 == | ||
The important | The important steps before generating CSV file. The CSV file will be converted to Excel file more convenient. | ||
* Add '''delimiter character''' between different column ex: comma symbol (,) or tab symbol | * Add '''delimiter character''' between different column ex: comma symbol (,) or tab symbol | ||
* Add '''enclosure character''' ex: double quote symbol (") or single quote symbol (') | * Add '''enclosure character''' ex: double quote symbol (") or single quote symbol (') | ||
Revision as of 11:09, 10 November 2015
Export MySQL query to Excel file (XLS or XLSX file)
Tools
one step conversion: ex MySQL query -> XLS/XLSX file
- PHPExcel
- other MySQL client softwares
two steps conversion: ex: CSV --> XLSX or XLS --> XLSX
- PHPExcel or MySQL client softwares + MS Excel
- PHPExcel or MySQL client softwares + LibreOffice Calc
Technical issues: If it costs too much time to convert to Excel file
The important steps before generating CSV file. The CSV file will be converted to Excel file more convenient.
- 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', ' ')