14,962
edits
Export MySQL query to Excel file: Difference between revisions
Jump to navigation
Jump to search
→Step 1: Export the MySQL query -> CSV file
| Line 77: | Line 77: | ||
==== Step 1: Export the MySQL query -> CSV file ==== | ==== Step 1: Export the MySQL query -> CSV file ==== | ||
* | Approaches | ||
* [[Database management tools]] (MySQL client softwares) | |||
* PHPExcel | |||
* [http://dev.mysql.com/doc/refman/5.7/en/mysqldump-delimited-text.html MySQL :: MySQL 5.7 Reference Manual :: 8.4.3 Dumping Data in Delimited-Text Format with mysqldump] | * [http://dev.mysql.com/doc/refman/5.7/en/mysqldump-delimited-text.html MySQL :: MySQL 5.7 Reference Manual :: 8.4.3 Dumping Data in Delimited-Text Format with mysqldump] | ||
** Required {{kbd | key = FILE}} permission ex: {{kbd | key = GRANT FILE ON *.* TO 'user_name'@'localhost';}} If you met the following error message: | ** Required {{kbd | key = FILE}} permission ex: {{kbd | key = GRANT FILE ON *.* TO 'user_name'@'localhost';}} If you met the following error message: | ||
| Line 84: | Line 86: | ||
</pre> | </pre> | ||
Condition 1: If the rows count of MySQL query is larger than 65,535 rows. You can use MS Excel. | Condition | ||
* Condition 1: If the rows count of MySQL query is larger than 65,535 rows. You can use MS Excel. | |||
Condition 2: If the rows count of MySQL query is less than 65,535 rows. You can use [https://zh-tw.libreoffice.org/discover/calc/ LibreOffice Calc] & MS Excel. | * Condition 2: If the rows count of MySQL query is less than 65,535 rows. You can use [https://zh-tw.libreoffice.org/discover/calc/ LibreOffice Calc] & MS Excel. | ||
* Condition 3: Replace the double quote with double double quote if the enclosure character is double quote. | |||
Condition 3: Replace the double quote with double double quote if the enclosure character is double quote. | * Condition 4: (optional) Replace the return character with space character or other character e.g. {{kbd | key = ↵}} ([https://www.compart.com/en/unicode/U+21B5 U+21B5]). SQL query syntax: | ||
Condition 4: (optional) Replace the return character with space character or other character e.g. {{kbd | key = ↵}} ([https://www.compart.com/en/unicode/U+21B5 U+21B5]). SQL query syntax: | |||
<pre> | <pre> | ||
REPLACE(REPLACE(REPLACE(column_name, '\r\n', ''), '\n', ''), '\r', '') AS column_name | REPLACE(REPLACE(REPLACE(column_name, '\r\n', ''), '\n', ''), '\r', '') AS column_name | ||