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 85: | Line 85: | ||
Condition 1: If the rows count of MySQL query is larger than 65,535 rows. You can use MS Excel. | 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 return character. | Condition 3: (optional) Replace the return character. 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 | ||
</pre> | </pre> | ||
validation: Use the editor supported [[Regular expression]], search {{kbd | key=<nowiki>^[^"]</nowiki>}} in the csv files to search the rows which contains return symbols. | |||
==== Step 2: Convert CSV to XLSX/XLS file format. ==== | ==== Step 2: Convert CSV to XLSX/XLS file format. ==== | ||