Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
2  
Can't you use the SELECT... INTO OUTFILE syntax to create a CSV file directly? – steeldriver Mar 8 at 7:11
    
@steeldriver : how to use. I'm a new one in this mysql – user476625 Mar 8 at 7:13
1  
Move to the directory where you would like to save your ("excel") file using cd. Then run mysql -u root -ppass123 -e "use test_db; select * into outfile 'testtable.csv' from test_table;". It will produce a file called testtable.csv in this directory, that you can probably open with excel/libreOffice Look into this link for more specific info on how to use select ... into outfile: dev.mysql.com/doc/refman/5.7/en/select-into.html – Gasp0de Mar 8 at 7:22
1  
Save in CSV format then import the CSV file to excel. stackoverflow.com/questions/356578/… – Parto Mar 8 at 7:23
    
@Gasp0de Would you please convert your comment into an answer? – Byte Commander Mar 8 at 7:29

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

share|improve this answer

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
share|improve this answer
    
i need to add a output to file on 6th column. how can i do – user476625 Mar 8 at 12:55
    
@user476625 Not sure what you mean. Can you clarify? You want 5 empty columns before the first one? Please edit your question adding an example. – kos Mar 8 at 20:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.