Microsoft Excel: Difference between revisions

Jump to navigation Jump to search

Microsoft Excel (edit)

Revision as of 16:36, 9 October 2016

1,754 bytes removed ,  9 October 2016
→‎Export MySQL query to Excel file
Line 1: Line 1:


== Export MySQL query to Excel file ==
== Export MySQL query to Excel file ==
Excel limit:
[[Export MySQL query to Excel file]]
# "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>}}
 
相關文章
* [http://errerrors.blogspot.com/2015/07/excel.html Err: 解決從資料庫輸出 Excel 檔案，遇到非常大數字的科學符號和有效位數的問題]
* [http://errerrors.blogspot.com/2016/02/navicat.html Err: 解決 Navicat 「存放裝置空間不足，無法完成此操作」問題訊息]


== Export CSV from EXCEL ==
== Export CSV from EXCEL ==
Retrieved from "https://wiki.planetoid.info/index.php/Special:MobileDiff/17398"

Navigation menu