Export MySQL query to Excel file: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
 
(23 intermediate revisions by the same user not shown)
Line 1: Line 1:
Export MySQL query to Excel file (XLS or XLSX file)
Export MySQL query to Excel file (XLS or XLSX file)


== Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits ==
<table border="1">
<tr>
  <th>Title</th>
  <th>File extension</th>
  <th>Data rows size</th>
  <th>Length of cell</th>
  <th>Available solutions</th>
</tr>
<tr>
  <td>Microsoft Excel Open XML Spreadsheet</td>
  <td>XLSX</td>
  <td>Maximum of worksheet size is 1,048,576 rows by 16,384 columns.<ref>[https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3?ui=en-US&rs=en-US&ad=US&fromAR=1 Excel specifications and limits]</ref><ref>[http://office.microsoft.com/zh-tw/excel-help/HP010073849.aspx Excel 的規格及限制] 工作表大小</ref><ref>[http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HA103980614.aspx Excel specifications and limits - Excel]</ref> </td>
  <td>(1) Length of formula contents: 8,192 characters <br />(2) Total number of characters that a cell can contain: 32,767 characters<ref>[http://errerrors.blogspot.com/2016/02/navicat.html Err: 解決 Navicat 「存放裝置空間不足，無法完成此操作」問題訊息]</ref></td>
  <td>Microsoft Excel 2007, EXCEL 2013 and [https://fileinfo.com/extension/xlsx more]</td>
</tr>
<tr>
  <td>Microsoft Excel Spreadsheet</td>
  <td>XLS</td>
  <td>Maximum of worksheet size is 65,536 rows by 256 columns. <br />{{exclaim}} Because first row is column name, actual data size is 65,535 rows. <ref>[http://superuser.com/questions/366468/what-is-the-maximum-allowed-rows-in-a-microsoft-excel-xls-or-xlsx What is the maximum allowed rows in a Microsoft Excel .xls or .xlsx - Super User]</ref><ref>[https://support.office.com/en-us/article/Worksheet-compatibility-issues-f9c80c5b-5afc-40da-a841-b888746abd40 Worksheet compatibility issues - Excel].</ref></td>
  <td></td>
  <td>Microsoft Excel 2003 and [https://fileinfo.com/extension/xls more]</td>
</tr>
<tr>
  <td>OpenDocument Spreadsheet</td>
  <td>ODS</td>
  <td>Maximum of worksheet size is 1,048,576 (2^20) rows by 1024 columns for LibreOffice spreadsheet & OpenOffice.org Calc 3 <ref>[https://wiki.documentfoundation.org/Faq/Calc/022 Frequently asked questions - Calc - The Document Foundation Wiki]</ref><ref>[https://simple.wikipedia.org/wiki/OpenOffice_Calc OpenOffice Calc - Simple English Wikipedia, the free encyclopedia]</ref>. </td>
  <td>Total number of characters that a cell can contain: 32,767 characters for LibreOffice spreadsheet<ref>[https://wiki.documentfoundation.org/Faq/Calc/022 Frequently asked questions - Calc - The Document Foundation Wiki]</ref>.</td>
  <td>[http://www.libreoffice.org/ LibreOffice]([http://portableapps.com/apps/office/libreoffice_portable/ LibreOffice Portable])  spreadsheet 3.3.3+ & Apache [https://www.openoffice.org/ OpenOffice.org] Calc 3+ and [https://fileinfo.com/extension/ods more]</td>
</tr>
</table>
* Google spreadsheet:
** 約 1000列。 所有工作表共 40 萬個儲存格。如果只使用一個工作表，使用256 欄，則列數限制為 1,562 列<ref>[https://support.google.com/drive/answer/37603?hl=zh-Hant Google 文件、試算表和簡報的檔案大小限制 - 雲端硬碟說明]</ref>
* Office 365: [http://office.microsoft.com/zh-tw/excel-help/HA102837464.aspx 資料模型的規格與限制 - Excel - Office.com] 適用: Excel 2013, Power BI for Office 365 Preview, Power Pivot in Excel 2013


== Methods ==
== Methods ==
Line 41: Line 6:
Methods of exporting MySQL query -> XLS/XLSX file: PHP scripts or other MySQL client softwares e.g.
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://phpexcel.codeplex.com/ PHPExcel]: PHP scripts
* [https://www.quest.com/products/toad-for-mysql/ Toad for MySQL] for {{Win}}
* Using [[Database management tools]] e.g. [https://www.quest.com/products/toad-for-mysql/ Toad for MySQL] for {{Win}}, ''$'' [https://www.navicat.com/cht Navicat GUI] for {{Win}} & {{Mac}}
* ''$'' [https://www.navicat.com/cht Navicat GUI] for {{Win}} & {{Mac}}


Notice the limit of Excel XLS file format:
{{exclaim}} Notice the limit of Excel XLS file format:
# "Total number of characters that a cell can contain: 32,767 characters"  
# "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 檔)
#* 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 檔)
Line 57: Line 21:


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


=== Two steps conversion ex: MySQL query -> CSV file -> Excel file===
=== Two steps conversion ex: MySQL query -> CSV file -> Excel file===


==== Step 1: Export the MySQL query -> CSV file ====
==== Step 1: Export the MySQL query -> CSV file ====
* PHPExcel or MySQL client softwares
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 70: Line 36:
</pre>
</pre>


Condition 1: If the rows count of MySQL query is larger than 65,535 rows. You can use MS Excel.
Condition
* Cell contains return symbol should be replaced.
* 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 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. ====
==== Step 2: Convert CSV to XLSX/XLS file format. ====
Line 78: Line 52:
* MS Excel
* 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://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]
** [http://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/2010/10/csv-microsoft-excel-2007-openofficeorg.html Err: 解決CSV檔亂碼 (Microsoft Excel 2007 或 OpenOffice.org 3.2)] {{Win}}
** [http://errerrors.blogspot.com/2014/05/mac-office-excel-csv-microsoft-excel.html Err: Mac Office Excel 匯入中文 CSV 檔案 (Microsoft Excel for Mac 2011)] {{Mac}}
** [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])
* [https://zh-tw.libreoffice.org/discover/calc/ LibreOffice Calc] ([http://portableapps.com/apps/office/libreoffice_portable LibreOffice Portable])


Line 103: Line 77:
=== Data format in result set became string after exported as Excel file===
=== Data format in result set became string after exported as Excel file===
* '''Number''' data types in result set became '''string''' after exported as XLSX file <ref>[https://exceljet.net/formula/convert-text-to-numbers Excel formula: Convert text to numbers | Exceljet]</ref>
* '''Number''' data types in result set became '''string''' after exported as XLSX file <ref>[https://exceljet.net/formula/convert-text-to-numbers Excel formula: Convert text to numbers | Exceljet]</ref>
* '''Date time''' data types in result set became '''string''' after exported as XLSX file<ref>[https://errerrors.blogspot.tw/2016/12/excel-value-text.html Excel 如何轉換文字格式的日期時間格式 (使用 VALUE, TEXT 函數)]</ref>
* '''Date time''' data types in result set became '''string''' after exported as XLSX file<ref>[https://errerrors.blogspot.com/2016/12/excel-value-text.html Excel 如何轉換文字格式的日期時間格式 (使用 VALUE, TEXT 函數)]</ref>
 
== Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits ==
[[Microsoft Excel#Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits]]


== Database management tools ==
[[Database management tools]]


== References ==
== References ==
<references/>
<references/>
{{Template:Data factory flow}}


[[Category:Data Science]] [[Category:Excel]] [[Category:MySQL]]
[[Category:Data Science]] [[Category:Excel]] [[Category:MySQL]]

Latest revision as of 18:48, 14 April 2026

Export MySQL query to Excel file (XLS or XLSX file)


Methods[edit]

One step conversion: ex MySQL query -> Excel file[edit]

Methods of exporting MySQL query -> XLS/XLSX file: PHP scripts or other MySQL client softwares e.g.

Icon_exclaim.gif Notice the limit of Excel XLS file format:

  1. "Total number of characters that a cell can contain: 32,767 characters"
    • Using MySQL SUBSTRING() function ex: SUBSTRING(string_column, 1, 32767) for EXCEL 97[1]-2007[2] (XLS 檔)
    • validate the count of characters: MySQL CHAR_LENGTH or Excel LEN functions
  2. "Length of formula contents: 8,192 characters" If the content start with the symbol = or -, Excel will consider the content is formula.
    • Using CONCAT ' symbol ex: CONCAT('\'', string_column) If the content is not formula.
  3. "Worksheet size: 1,048,576 rows":
    • Using LIMIT 1048576 for EXCEL 2007

integrated solution for Excel limit 1 ~ limit 3

  • SELECT SUBSTRING(IF(LEFT(string_column, 1) = '-' OR LEFT(string_column, 1) = '=', CONCAT('\'', string_column), string_column), 1, 32767) FROM <table> LIMIT 1048576

相關文章

Two steps conversion ex: MySQL query -> CSV file -> Excel file[edit]

Step 1: Export the MySQL query -> CSV file[edit]

Approaches 

mysqldump: Got error: 1045: Access denied for user 'user_name'@'localhost' (using password: YES) when executing 'SELECT INTO OUTFILE'

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 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. ↵ (U+21B5). SQL query syntax:
REPLACE(REPLACE(REPLACE(column_name, '\r\n', ''), '\n', ''), '\r', '') AS column_name

Validation: Use the editor supported Regular expression,

  • Search ^[^"] in the csv files to search the rows which contains return symbols. The " symbol is the "text qualifier" what I specified before export the MySQL query.

Step 2: Convert CSV to XLSX/XLS file format.[edit]

Step 3: Data verification[edit]

  1. 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.
  2. Verify the data column is in correct position
    • Icon_exclaim.gif If the CSV is not well-formatted, sometimes the data column after imported to Excel is in wrong position.
  3. Verify the rows count
    • If the data size is less than 65,535 rows, convert CSV to Excel/XLS by 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. Tab key
      • escape the enclosure character e.g. " become ""
      • return symbol to separate different data row.

Troubleshooting of technical issues[edit]

If it costs too much time to convert to Excel file[edit]

The important steps before generating CSV file. These steps reduce the problems when the CSV file was converted to Excel file.

  • Add delimiter character between different column ex: comma symbol (,) or tab symbol
  • Add enclosure character ex: double quote symbol (") or single quote symbol (')
  • Add escape character escape the column value if contains the enclosure character
  • Remove return symbol[3]: 
    SELECT REPLACE(REPLACE(REPLACE(`my_column`, '\r\n', '   '), '\n', '   '), '\r', '   ')

Data format in result set became string after exported as Excel file[edit]

  • Number data types in result set became string after exported as XLSX file [4]
  • Date time data types in result set became string after exported as XLSX file[5]

Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits[edit]

Microsoft Excel#Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits


References[edit]

  1. ↑ Summary of capability limitations in Excel 97
  2. ↑ Excel specifications and limits
  3. ↑ trim - How to remove new line characters from data rows in mysql? - Stack Overflow
  4. ↑ Excel formula: Convert text to numbers | Exceljet
  5. ↑ Excel 如何轉換文字格式的日期時間格式 (使用 VALUE, TEXT 函數)

Data factory flow

Retrieved from "https://wiki.planetoid.info/index.php?title=Export_MySQL_query_to_Excel_file&oldid=26314"