Export MySQL query to Excel file: Difference between revisions
Jump to navigation
Jump to search
Line 85: | Line 85: | ||
# 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 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 | # Verify the data column is in correct position | ||
#* {{exclaim}} | #* {{exclaim}} If 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 [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 | #* 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 | #** enclosure character e.g. {{kbd | key=Tab}} key |
Revision as of 18:43, 18 September 2017
Export MySQL query to Excel file (XLS or XLSX file)
Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits
Title | File extension | Data rows size | Length of cell | Available solutions |
---|---|---|---|---|
Microsoft Excel Open XML Spreadsheet | XLSX | Maximum of worksheet size is 1,048,576 rows by 16,384 columns.[1][2][3] | (1) Length of formula contents: 8,192 characters (2) Total number of characters that a cell can contain: 32,767 characters[4] |
Microsoft Excel 2007, EXCEL 2013 and more |
Microsoft Excel Spreadsheet | XLS | Maximum of worksheet size is 65,536 rows by 256 columns. Because first row is column name, actual data size is 65,535 rows. [5][6] |
Microsoft Excel 2003 and more | |
OpenDocument Spreadsheet | ODS | Maximum of worksheet size is 1,048,576 (2^20) rows by 1024 columns for LibreOffice spreadsheet & OpenOffice.org Calc 3 [7][8]. | Total number of characters that a cell can contain: 32,767 characters for LibreOffice spreadsheet[9]. | LibreOffice(LibreOffice Portable) spreadsheet 3.3.3+ & Apache OpenOffice.org Calc 3+ and more |
- Google spreadsheet:
- 約 1000列。 所有工作表共 40 萬個儲存格。如果只使用一個工作表,使用256 欄,則列數限制為 1,562 列[10]
- Office 365: 資料模型的規格與限制 - Excel - Office.com 適用: Excel 2013, Power BI for Office 365 Preview, Power Pivot in Excel 2013
Methods
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.
- PHPExcel: PHP scripts
- Toad for MySQL for Win
- $ 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 MySQL SUBSTRING() function ex: SUBSTRING(string_column, 1, 32767) for EXCEL 97[11]-2007[12] (XLS 檔)
- validate the count of characters: MySQL CHAR_LENGTH or Excel LEN functions
- "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.
- "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
- PHPExcel or MySQL client softwares
- MySQL :: MySQL 5.7 Reference Manual :: 8.4.3 Dumping Data in Delimited-Text Format with mysqldump
- Required FILE permission ex: GRANT FILE ON *.* TO 'user_name'@'localhost'; If you met the following error message:
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.
- MS Excel
- LibreOffice Calc (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
- If 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[13]:
SELECT REPLACE(REPLACE(REPLACE(`my_column`, '\r\n', ' '), '\n', ' '), '\r', ' ')
Database management tools
References
- ↑ Excel specifications and limits
- ↑ Excel 的規格及限制 工作表大小
- ↑ Excel specifications and limits - Excel
- ↑ Err: 解決 Navicat 「存放裝置空間不足,無法完成此操作」問題訊息
- ↑ What is the maximum allowed rows in a Microsoft Excel .xls or .xlsx - Super User
- ↑ Worksheet compatibility issues - Excel.
- ↑ Frequently asked questions - Calc - The Document Foundation Wiki
- ↑ OpenOffice Calc - Simple English Wikipedia, the free encyclopedia
- ↑ Frequently asked questions - Calc - The Document Foundation Wiki
- ↑ Google 文件、試算表和簡報的檔案大小限制 - 雲端硬碟說明
- ↑ Summary of capability limitations in Excel 97
- ↑ Excel specifications and limits
- ↑ trim - How to remove new line characters from data rows in mysql? - Stack Overflow