Export MySQL query to Excel file: Difference between revisions
Jump to navigation
Jump to search
| Line 11: | Line 11: | ||
* 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>: Maximum of worksheet size is 1,048,576 rows by 16,384 columns. | * 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>: Maximum of worksheet size is 1,048,576 rows by 16,384 columns. | ||
=== | === Two steps conversion ex: MySQL query -> CSV file -> Excel file=== | ||
Step 1: Export the MySQL query -> CSV/XLS file | |||
* PHPExcel or MySQL client softwares | * PHPExcel or MySQL client softwares | ||
* [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 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: | ||
<pre> | <pre> | ||
mysqldump: Got error: 1045: Access denied for user 'user_name'@'localhost' (using password: YES) when executing 'SELECT INTO OUTFILE' | mysqldump: Got error: 1045: Access denied for user 'user_name'@'localhost' (using password: YES) when executing 'SELECT INTO OUTFILE' | ||
</pre> | </pre> | ||
Step 2: Import CSV to XLSX or XLS file format. | |||
* 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] | |||
** [http://errerrors.blogspot.tw/2010/10/csv-microsoft-excel-2007-openofficeorg.html Err: 解決CSV檔亂碼 (Microsoft Excel 2007 或 OpenOffice.org 3.2)] {{Win}} | |||
** [http://errerrors.blogspot.tw/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] | * [https://zh-tw.libreoffice.org/discover/calc/ LibreOffice Calc] | ||
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 | |||
#* {{exclaim}} 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 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 enclosure character e.g. {{kbd | key=Tab}} key and return symbol to separate different data row. | |||
== Technical issues: If it costs too much time to convert to Excel file == | == Technical issues: If it costs too much time to convert to Excel file == | ||
Revision as of 12:00, 28 September 2016
Export MySQL query to Excel file (XLS or XLSX file)
Tools
One step conversion: ex MySQL query -> Excel file
Methods of exporting MySQL query -> XLS/XLSX file
- PHPExcel
- other MySQL client softwares
Notice the limit of Excel XLS file format:
- XLS file format[1][2]: Maximum of worksheet size is 65,536 rows by 256 columns. First row is column name.
Actual data size is 65,535 rows. - XLSX file format [3]: Maximum of worksheet size is 1,048,576 rows by 16,384 columns.
Two steps conversion ex: MySQL query -> CSV file -> Excel file
Step 1: Export the MySQL query -> CSV/XLS 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'
Step 2: Import CSV to XLSX or XLS file format.
- MS Excel
- LibreOffice Calc
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
- 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 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 and 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[4]:
SELECT REPLACE(REPLACE(REPLACE(`my_column`, '\r\n', ' '), '\n', ' '), '\r', ' ')