Export MySQL query to Excel file: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
Line 54: Line 54:
=== One step conversion: ex MySQL query -> Excel file ===
=== 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.
Methods of exporting MySQL query -> XLS/XLSX file: PHP scripts or other MySQL client softwares e.g.
* [https://www.spectralcore.com/fullconvert]: 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.
* [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}}
* [https://www.quest.com/products/toad-for-mysql/ Toad for MySQL] for {{Win}}

Revision as of 01:35, 17 September 2018

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.
Icon_exclaim.gif Because first row is occupied by 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 "Up to 2 million cells for spreadsheets that are created in or converted to Google Sheets"
2,000,000 cells = 7,812 rows x 256 columns per sheet.[10]
Zoho Sheet "Up to 1 million cells for a single spreadsheet with a maximum number of 65536 rows and 256 columns per sheet."[11]


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.

  • [1]: 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.
  • PHPExcel: PHP scripts
  • Toad for MySQL for Win Os windows.png
  • $ Navicat GUI for Win Os windows.png & Mac icon_os_mac.png

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[12]-2007[13] (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

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.

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 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

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

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 [15]
  • Date time data types in result set became string after exported as XLSX file[16]

Database management tools

Database management tools

References