Export MySQL query to Excel file: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
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) file limits ==
== Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits ==
<table border="1">
<table border="1">
  <tr>
  <tr>
   <th>file format</th>
   <th>file format</th>
  <th>file extension</th>
   <th>data rows size</th>
   <th>data rows size</th>
   <th>length of cloumn</th>
   <th>length of cell</th>
  </tr>
  </tr>
  <tr>
  <tr>
   <td>XLSX file format<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></td>
  <td>Microsoft Excel 2007</td>
   <td>XLSX<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></td>
   <td>Maximum of worksheet size is 1,048,576 rows by 16,384 columns. </td>
   <td>Maximum of worksheet size is 1,048,576 rows by 16,384 columns. </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>(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>
  </tr>
  </tr>
  <tr>
  <tr>
   <td>XLS file format<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>Microsoft Excel 2003</td>
   <td>XLS<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>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. </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. </td>
   <td></td>
   <td></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>
  </tr>
  </tr>
</table>
</table>

Revision as of 12:03, 15 July 2017

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

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

file format file extension data rows size length of cell
Microsoft Excel 2007 XLSX[1] Maximum of worksheet size is 1,048,576 rows by 16,384 columns. (1) Length of formula contents: 8,192 characters
(2) Total number of characters that a cell can contain: 32,767 characters[2]
Microsoft Excel 2003 XLS[3][4] Maximum of worksheet size is 65,536 rows by 256 columns.
Icon_exclaim.gif Because first row is column name, actual data size is 65,535 rows.
OpenDocument Spreadsheet ODS Maximum of worksheet size is 1,048,576 (2^20) rows by 1024 columns for LibreOffice spreadsheet & OpenOffice.org Calc 3 [5][6]. Total number of characters that a cell can contain: 32,767 characters for LibreOffice spreadsheet[7].

Methods

One step conversion: ex MySQL query -> Excel file

Methods of exporting MySQL query -> XLS/XLSX file

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[8]-2007[9] (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

Step 1: Export the MySQL query -> CSV file

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

Condition 1: If the rows count of MySQL query is larger than 65,535 rows. You can use MS Excel.

  • Cell contains return symbol should be replaced.

Condition 2: If the rows count of MySQL query is less than 65,535 rows. You can use LibreOffice Calc & MS Excel.

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

Step 3: Data verification

  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 Because the CSV is not well-formatted, sometimes the data column after imported to Excel is in wrong position. 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.

Technical issues: If it costs too much time to convert to Excel file

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[10]: 
    SELECT REPLACE(REPLACE(REPLACE(`my_column`, '\r\n', '   '), '\n', '   '), '\r', '   ')

References

  1. ↑ Excel specifications and limits
  2. ↑ Err: 解決 Navicat 「存放裝置空間不足，無法完成此操作」問題訊息
  3. ↑ What is the maximum allowed rows in a Microsoft Excel .xls or .xlsx - Super User
  4. ↑ Worksheet compatibility issues - Excel.
  5. ↑ Frequently asked questions - Calc - The Document Foundation Wiki
  6. ↑ OpenOffice Calc - Simple English Wikipedia, the free encyclopedia
  7. ↑ Frequently asked questions - Calc - The Document Foundation Wiki
  8. ↑ Summary of capability limitations in Excel 97
  9. ↑ Excel specifications and limits
  10. ↑ trim - How to remove new line characters from data rows in mysql? - Stack Overflow
Retrieved from "https://wiki.planetoid.info/index.php?title=Export_MySQL_query_to_Excel_file&oldid=18353"