take output from the mysql and stored into excel

Question

i have database under that one table. for eg test_table. in that

  name age sex edu   job    timein              doj
  yyy   33  m   bsc  comp   22/3/13 12:32:44    3/2/11
  xxx   22  f   mca  soft   22/3/13 11:30:11    11/10/09
  zzz   44  f   puc  clerk  22/3/13 12:00:24    5/11/08

command

 mysql -u root -ppass123  -e "use test_db; select name,age, sex,edu,job,timein,doj from test_table;"

how to convert into excel

Answer 1

Move to the directory where you would later like to have your CSV file saved. Then run

mysql -u root -ppass123 -e "USE test_db; SELECT * INTO OUTFILE 'testtable.csv' FROM test_table;"

Please note that "SELECT *" selects all columns, while you listed all of them. If you want to select only certain columns, you will need to replace * by the columns names again, separated by commas.

It will produce a file called testtable.csv in this directory, that you can open with MS Excel or LibreOffice Calc.

Look into the MySQL documentation for SELECT ... INTO OUTFILE here for further info:
http://dev.mysql.com/doc/refman/5.7/en/select-into.html

Answer 2

Another way would be storing the output directly in a .xls spreadsheet;

That can be done by piping the output of mysql to ssconvert (in the gnumeric package), but requires ssconvert's input file to be either a CSV or a TSV.

So you could add the -B option to the mysql command (which forces mysql to output a TSV) and pipe the output of the mysql command to ssconvert:

mysql -u root -ppass123  -B -e "use test_db; select name,age, sex,edu,job,timein,doj from test_table;" |\
ssconvert fd://0 output_file.xls