0

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

6
  • 2
    Can't you use the SELECT... INTO OUTFILE syntax to create a CSV file directly? Commented Mar 8, 2016 at 7:11
  • @steeldriver : how to use. I'm a new one in this mysql
    – Beginner
    Commented Mar 8, 2016 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
    Commented Mar 8, 2016 at 7:22
  • 1
    Save in CSV format then import the CSV file to excel. stackoverflow.com/questions/356578/…
    – Parto
    Commented Mar 8, 2016 at 7:23
  • @Gasp0de Would you please convert your comment into an answer?
    – Byte Commander
    Commented Mar 8, 2016 at 7:29

2 Answers 2

6

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

0
1

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
2
  • i need to add a output to file on 6th column. how can i do
    – Beginner
    Commented Mar 8, 2016 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
    Commented Mar 8, 2016 at 20:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .