Getting Data from MySQL into Excel
Posted by Corban Brook Tue, 02 Jan 2007 18:37:33 GMT
The other title of this article could be Outputting data from MySQL into a Comma Separated Value file (.csv)
Short and sweet, this article is more for me to store this SQL syntax in an easily recoverable place as I seem to forget how to use it every time I need to output something into CSV format from MySQL.
Saving a SELECT into CSV format
I find the easiest way to getting something into MS Excel is to just output it from MySQL into a comma seperated value file or .csv.
Note: This will not work in all cases, if your data contains many commas and quotations it may break the formating and rows will be fragmented. In most simple cases it will work fine.
SELECT * FROM people INTO OUTFILE '/your/home/directory/output.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';Make sure the path to the output.csv file is somewhere your user has permissions to write to.
Although there are other ways like setting up an ODBC adapter and suck data into Access and saving into an xls format, I beleive this to be the quickest way for most cases.
Excellent ! I think this is the best way to export as Excel. It is really great to because it use the database server nor the web server. (load balancing)
Very good idea!~