Editing Export MySQL query to Excel file (section)

=== One step conversion: ex MySQL query -> Excel file ===
Methods of exporting MySQL query -> XLS/XLSX file: PHP scripts or other MySQL client softwares e.g.
* [https://phpexcel.codeplex.com/ PHPExcel]: PHP scripts
* [https://www.quest.com/products/toad-for-mysql/ Toad for MySQL] for {{Win}}
* [https://www.spectralcore.com/fullconvert Full Convert Enterprise]: allows easy copying of tables and data between 40 or so supported databases. It will create all the tables, copy all of your data, then create indexes and foreign keys. 30 days trial
* ''$'' [https://www.navicat.com/cht Navicat GUI] for {{Win}} & {{Mac}}

Notice the limit of Excel XLS file format:
# "Total number of characters that a cell can contain: 32,767 characters" 
#* Using [http://www.w3resource.com/mysql/string-functions/mysql-substring-function.php MySQL SUBSTRING() function] ex: {{kbd | key = <nowiki>SUBSTRING(string_column, 1, 32767)</nowiki>}} for EXCEL 97<ref>[https://support.microsoft.com/en-us/kb/296053 Summary of capability limitations in Excel 97]</ref>-2007<ref>[https://support.office.com/en-us/article/Excel-specifications-and-limits-16c69c74-3d6a-4aaf-ba35-e6eb276e8eaa?CorrelationId=1de7e8d2-91e2-4eec-8868-4dc8cbd23463&ui=en-US&rs=en-US&ad=US Excel specifications and limits]</ref> (XLS 檔)
#* validate the count of characters: MySQL {{kbd | key = <nowiki>CHAR_LENGTH</nowiki>}} or Excel {{kbd | key = <nowiki>LEN</nowiki>}} functions
# "Length of formula contents: 8,192 characters" If the content start with the symbol {{kbd | key =<nowiki>=</nowiki>}} or {{kbd | key =<nowiki>-</nowiki>}}, Excel will consider the content is formula. 
#* Using CONCAT {{kbd | key =<nowiki>'</nowiki>}} symbol ex: {{kbd | key = <nowiki>CONCAT('\'', string_column)</nowiki>}} If the content is not formula.
# "Worksheet size: 1,048,576 rows": 
#* Using {{kbd | key =LIMIT 1048576}} for EXCEL 2007

integrated solution for Excel limit 1 ~ limit 3
*  {{kbd | key =<nowiki>SELECT SUBSTRING(IF(LEFT(string_column, 1) = '-' OR LEFT(string_column, 1) = '=', CONCAT('\'', string_column), string_column), 1, 32767) FROM <table> LIMIT 1048576</nowiki>}}

相關文章
* [https://errerrors.blogspot.com/2015/07/excel.html Err: 解決從資料庫輸出 Excel 檔案，遇到非常大數字的科學符號和有效位數的問題]
* [https://errerrors.blogspot.com/2016/02/navicat.html Err: 解決 Navicat 「存放裝置空間不足，無法完成此操作」問題訊息]