Export MySQL query to Excel file: Difference between revisions
Jump to navigation
Jump to search
| Line 39: | Line 39: | ||
* 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. | ** [http://errerrors.blogspot.com/2010/10/csv-microsoft-excel-2007-openofficeorg.html Err: 解決CSV檔亂碼 (Microsoft Excel 2007 或 OpenOffice.org 3.2)] {{Win}} | ||
** [http://errerrors.blogspot. | ** [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://zh-tw.libreoffice.org/discover/calc/ LibreOffice Calc] | * [https://zh-tw.libreoffice.org/discover/calc/ LibreOffice Calc] | ||
Revision as of 12:21, 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:
| file format | data rows size | length of cloumn |
|---|---|---|
| XLSX file format[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] |
| XLS file format[3][4] | Maximum of worksheet size is 65,536 rows by 256 columns. First row is column name.  Actual data size is 65,535 rows. |
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'
Step 2: Import CSV to XLSX/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/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. "
- 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[5]:
SELECT REPLACE(REPLACE(REPLACE(`my_column`, '\r\n', ' '), '\n', ' '), '\r', ' ')