How to fetch data from MySQL database into Excel

How to fetch data from MySQL databas开发者_如何学JAVAe table into Excel?

1. Install Connector/ODBC

2. Create an ODBC DSN (Control Panel / Administration / ODBC if on Windows)

3. Import data using Microsoft Query (Data / External Data / From Other Sources / Microsoft Query in Excel 2007)

If your server is also your client machine you can do

select * from table1 into outfile 'c:/path/outfile.csv'

(Note the use of forward slashes even on Windows)

If your server is a remote machine, make sure your have the MySQL client software installed on the client machine and do:

mysql -e "SELECT * from table1" > file_name

You can then open the csv file in Excel.

Try out with

SELECT column1, column1, column1 from tablename INTO OUTFILE "path/CSV/tablename.xls" FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\n" <br>

refer http://dev.mysql.com/doc/refman/5.1/en/load-data.html

I think the easiest way is ODBC. You can download the ODBC driver for MySQL (if you don't have it already). Then, once inside Excel, you can go to "Data-> Obtain external data" (the name and location of the menu items may vary) and pick or create a data source.

odbc-query.

excel: /data/get external data...