Tuesday, December 03, 2013

Exporting data from remote mysql instance in csv, using mysql client and sed

Mysql allows exporting query results to csv using the INTO OUTFILE, like the following example:


SELECT a,b,a+b INTO OUTFILE '/tmp/result.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;



This, however, will cause the data to be exported to the file system of the database server.  Sometimes you do not have access to that server and you are only connected remotely from a different machine using mysql command line.

One way to export the data is to pass a query and redirect the output to a file as follows



$ mysql -u USERNAME -p PASSWORD -h DB_SERVER mydb -e "SELECT a,b,a+b INTO OUTFILE
  FROM test_table;"  > output.txt



This will do the trick, except that the resulting file is TAB separated instead of CSV.

You can simply download the file and open it using any text editor and replace all tabs with commas. but that's not practical for large data sets.  Instead, we can use sed.  Sed is a powerful tool that can be used to replace text in streams.  If you are unfamiliar with it, i recommend you check it out. the simple example below describes the usage quickly



$ echo "this is original text"
this is original text
$ echo "this is original text" | sed -e's/original/manipulated/g'
this is manipulated text

Seems perfect. So we can only use it to replace TAB with COMMA.  but wait.  sed does not understand "\t"..   how can we pass it as part of the argument?!!

Luckly, brandizzi explained the proper way to do it. To write TAB into the command line, just hit CTRL+V then TAB

So our final step will be
$ sed -i -e 's/ /,/g' output.txt

Voila


3 comments:

Hossam Hammady said...

mysqldump is created for this reason, it will take care of encoding issues that plain mysql will face due to low level redirection on the filesystem.

mahmoud said...

mysqldump is another way of exporting data and is very suitable in most cases, but in the case here, I needed to export data to be passed to the besiness people and other 3rd party. We use CSV as a raw data format for information exchange with such people

Hossam Hammady said...

mysqldump can produce CSV tables:

--tab=path, -T path

Produce tab-separated text-format data files. For each dumped table, mysqldump creates a tbl_name.sql file that contains the CREATE TABLE statement that creates the table, and the server writes a tbl_name.txt file that contains its data. The option value is the directory in which to write the files.

Indeed plain mysql export will corrupt your data if the encoding of the filesystem is different than that of your data.