MySQL CSV , Tab separated Data Dump


Commonly we use the “mysqldump” tool to generate or backup data. But the standard output of the utility is SQL, or bunch of SQL statements starting from the “CREATE TABLE …” .. to the “INSERT INTO …”

Most of the times we just need a simple CSV or a tab separated data dump. And then we start searching for options, phpMyAdmin.

To get a CSV dump, the mysqldump utility needs some parameters.

mysqldump -T /folder_path –fields-terminated-by=’,’ –fields-enclosed-by='”‘ –lines-terminated-by=’\r\n’ -u root -p database_name table_name


Note: I’m unable to show double hyphen before fields-terminated-by
It should be something like this.

mysqldump

The above command will save 2 files “tablename.sql” and “tablename.txt”.
The tablename.sql will have a CREATE TABLE script while the tablename.txt will contain a comma separated data dump of the table. Both the files will be create in the folder “/folder_path”

A similar command is used to get a TAB separated dump

mysqldump -T /folder_path -u root -p database_name table_name

While using this command more than one table can be dumped, just list all the tables separated by a space

One Response to “MySQL CSV , Tab separated Data Dump”

  1. Wasif Says:

    Nice post, SELECT … INTO OUTFILE (http://dev.mysql.com/doc/refman/5.1/en/select.html) can also be an alternative.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: