Export MySQL query to Excel file: Difference between revisions
Jump to navigation
Jump to search
mNo edit summary |
|||
| Line 15: | Line 15: | ||
* Add '''enclosure character''' ex: double quote symbol (") or single quote symbol (') | * Add '''enclosure character''' ex: double quote symbol (") or single quote symbol (') | ||
* Add '''escape character''' escape the column value if contains the '''enclosure character''' | * Add '''escape character''' escape the column value if contains the '''enclosure character''' | ||
* Remove return symbol: <pre>SELECT REPLACE(REPLACE(REPLACE(`my_column`, '\r\n', ' '), '\n', ' '), '\r', ' ') </pre> | * Remove return symbol<ref>[http://stackoverflow.com/questions/1504962/how-to-remove-new-line-characters-from-data-rows-in-mysql trim - How to remove new line characters from data rows in mysql? - Stack Overflow]</ref>: <pre>SELECT REPLACE(REPLACE(REPLACE(`my_column`, '\r\n', ' '), '\n', ' '), '\r', ' ') </pre> | ||
== Reference == | |||
<Reference /> | |||
[[Category:Data Science]] [[Category:Excel]] | [[Category:Data Science]] [[Category:Excel]] | ||
Revision as of 14:08, 9 November 2015
Export MySQL query to Excel file (XLS or XLSX file)
Tools
- PHPExcel
- other MySQL client softwares
two steps conversion: ex
- CSV --> XLSX or
- XLS --> XLSX
Technical issues: If it costs too much time to convert to Excel file
First steps for 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', ' ')
Reference
<Reference />