Thursday, May 31, 2012

Dumping MySQL table into CSV file

Sponsored Links
Sometimes we need to get the MySQL tables into an Excel sheet. How this can be done? We can dump the MySQL tables into a csv format file. Lets see how to do it.


Login to your MySQL server.

[root@database ~]# mysql -p
Enter password:

Use the desired database
mysql> use database_name

Now for dumping the data of the table named "table_name" execute the command, the csv file will be save in /tmp. You can specify the destination in the command.

mysql> SELECT * INTO OUTFILE '/tmp/table_name.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table_name;
Query OK, 5000 rows affected (0.09 sec)
mysql>

If you need only first 250 RAWs, you can specify the limit as follows.

mysql> SELECT * INTO OUTFILE '/tmp/table_name.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table_name LIMIT 250;
Query OK, 250 rows affected (0.09 sec)
mysql>


Thats it. You can get the csv file to your windows system and can open with Microsoft Excel.

Check how to importing csv file to MySQL database table

Recommended Reading

1. Streaming Media Bible
2. Video Over IP, Second Edition: IPTV, Internet Video, H.264, P2P, Web TV, and Streaming: A Complete Guide to Understanding the Technology
3. Mastering Internet Video: A Guide to Streaming and On-Demand Video: A Guide to Streaming and On-Demand Video

Sponsored Links

No comments:

Post a Comment

Be nice. That's all.