Editing Export MySQL query to Excel file (section)

=== Two steps conversion ex: MySQL query -> CSV file -> Excel 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] 
** Required {{kbd | key = FILE}} permission ex: {{kbd | key = GRANT FILE ON *.* TO 'user_name'@'localhost';}} If you met the following error message: 
<pre>
mysqldump: Got error: 1045: Access denied for user 'user_name'@'localhost' (using password: YES) when executing 'SELECT INTO OUTFILE'
</pre>

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 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: 
<pre>
REPLACE(REPLACE(REPLACE(column_name, '\r\n', ''), '\n', ''), '\r', '') AS column_name
</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. The {{kbd | key=<nowiki>"</nowiki>}} symbol is the "text qualifier" what I specified before export the MySQL query.

==== Step 2: Convert CSV to XLSX/XLS file format. ====

* MS Excel
** [https://www.itg.ias.edu/content/how-import-csv-file-uses-utf-8-character-encoding-0 How to import a .csv file that uses UTF-8 character encoding | Information Technology Group]
** [https://errerrors.blogspot.com/2010/10/csv-microsoft-excel-2007-openofficeorg.html Err: 解決CSV檔亂碼 (Microsoft Excel 2007 或 OpenOffice.org 3.2)] {{Win}}
** [https://errerrors.blogspot.com/2014/05/mac-office-excel-csv-microsoft-excel.html Err: Mac Office Excel 匯入中文 CSV 檔案 (Microsoft Excel for Mac 2011)] {{Mac}}
* [https://zh-tw.libreoffice.org/discover/calc/ LibreOffice Calc] ([http://portableapps.com/apps/office/libreoffice_portable LibreOffice Portable])

==== Step 3: Data verification ====
# Verify the data rows count: Equal to the rows count of MySQL query. '''Not''' equal to 65,535 rows or less than the rows count of MySQL query.
# Verify the data column is in correct position
#* {{exclaim}} If the CSV is not well-formatted, sometimes the data column after imported to Excel is in wrong position. 
# Verify the rows count
#* If the data size is less than 65,535 rows, convert CSV to Excel/XLS by [https://zh-tw.libreoffice.org/discover/calc/ LibreOffice Calc] is recommended.
#* If the data size is larger than 65,535 rows, Adjust the MySQL query to avoid the column value contains the 
#** enclosure character e.g. {{kbd | key=Tab}} key 
#** escape the enclosure character e.g. {{kbd | key=<nowiki>"</nowiki>}} become {{kbd | key=<nowiki>""</nowiki>}}
#** return symbol to separate different data row.