blog.up-link.ro android bsd linux unix & open-source world

14Apr/100

How To Export MySQL data to CSV file

WP Greet Box icon
Hello there! If you are new here, you might want to subscribe to the RSS feed or subscribe via E-mail for updates on this topic.

In this article I'll show you how to export MySQL database to a CSV file.

1. Export MySQL data to CSV file using a simple "SELECT" statement

If you want to export your mysql table into a csv file you need to run the following command:

# mysql -u username -ppassword database -e "SELECT * INTO OUTFILE '/tmp/filename.csv'  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'  LINES TERMINATED BY '\r\n' FROM table;"

username is your mysql username
password is your mysql password
database is your mysql database
table is the table you want to export

You can find the exported CSV file in the /tmp directory. The name of the file is filename.csv.

2. Export MySQL data to CSV file using "sed"

If you want to export your mysql table into a csv file you need to run the following command:

# mysql -u username -ppassword database -B -e "SELECT * FROM table;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv

Explanation:

username is your mysql username
password is your mysql password
database is your mysql database
table is the table you want to export

The -B option will delimit the data using tabs and each row will appear on a new line.
The -e option denotes the MySQL command to run, in our case the "SELECT" statement.
The "sed" command used here contains three sed scripts:

s/\t/","/g;s/^/"/ - this will search and replace all occurences of 'tabs' and replace them with a ",".

s/$/"/; - this will place a " at the start of the line.

s/\n//g  - this will place a " at the end of the line.

You can find the exported CSV file in the current directory. The name of the file is filename.csv.

Print This Post Print This Post
Comments (0) Trackbacks (0)

No comments yet.


Leave a comment


*

No trackbacks yet.