Developing Webs

This tutorial will guide you to learn how to export data from MySQL table to the CSV (Comma-separated values) format. The CSV file format is a common file type that stores tabular data usually processed in applications like Microsoft Excel (XLS), each data separated with comma.

Export mysql data table to csv format using simple sql query, this sometime happened if you need to quickly dump bunch of MySQL tables into excel so you can view and play around with data. Actually you don’t need any 3rd party software for that. Mysql provide INTO OUTFILE command to write out (export) data result from table query into any file. The .csv extension is commonly used for saving data with comma separated value. To do this you just need mysql console and type query just like this

	SELECT * INTO OUTFILE 'C:\\my_excel_table.csv'
	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
	LINES TERMINATED BY '\r\n'
	FROM mytable;

The “C:\\my_excel_table.csv “ is where you want to save data table to, assuming you are using microsoft windows you can get the result in C:\my_excel_table.csv. The “FIELDS TERMINATED BY ‘,’” will export data with comma separated value, you can change this as you want, might be with tabular data ‘\t’ character. The OPTIONALLY ENCLOSED BY ‘”‘ will optionally close a long string with quote. Finally the LINES TERMINATED BY ‘\r\n’ will add a carriage return and new line (you might just need “\n” character in unix environtment). The FROM mytable; will use mytable as table source for query. And that’s it, you now can open it in your Excel and save as in other format such as web page (.htm) or excel it self (.xls).

A CSV file format is common use in data file, you can also open it using Open office or another software that support .csv format.

Related posts:

1. Export MySQL table to Excel file using php script
2. Creating user online program with php