15,039
edits
Export MySQL query to Excel file: Difference between revisions
Jump to navigation
Jump to search
→Technical issues: If it costs too much time to convert to Excel file
| Line 93: | Line 93: | ||
#** return symbol to separate different data row. | #** return symbol to separate different data row. | ||
== Technical issues | == 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. | 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 '''delimiter character''' between different column ex: comma symbol (,) or tab symbol | ||
| Line 100: | Line 101: | ||
* 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> | * 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> | ||
=== Data format in result set became string after exported as Excel file=== | |||
* '''Number''' data types in result set became '''string''' after exported as XLSX file: [https://exceljet.net/formula/convert-text-to-numbers Excel formula: Convert text to numbers | Exceljet] | |||
* '''Date time''' data types in result set became '''string''' after exported as XLSX file: [https://superuser.com/questions/274494/convert-text-string-to-date-time-format microsoft excel - convert text string to date/time format - Super User] ''unverified'' | |||
== Database management tools == | == Database management tools == | ||