?
Solved

How to export the result of mysql cmd to a text file?

Posted on 2006-11-29
4
Medium Priority
?
479 Views
Last Modified: 2013-11-18
Does anyone know how to export the result of mysql cmd to a text file?
0
Comment
Question by:kennysflau
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 19

Expert Comment

by:Kim Ryan
ID: 18043120
Use the -e option to run a command and quit, and redirect output to a text file.

mysql -u user -ppasswd -D dbase_name -e 'select * from table_name'  > report.txt
0
 
LVL 14

Accepted Solution

by:
ygoutham earned 200 total points
ID: 18043457
there is a separate command in my sql called select into outfile

http://dev.mysql.com/doc/refman/5.0/en/select.html

make sure that the file already does not exist before giving the command

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

in this instance the file "/tmp/result.txt" if it already exists then it gives a error.  it gives you a text file with , as the field separator to be imported into a excel, access, openoffice calc etc.
0
 
LVL 19

Expert Comment

by:Kim Ryan
ID: 18049784
This only works if you want a select statement. The -e option above will work with any statement, update, insert etc
0
 
LVL 14

Expert Comment

by:ygoutham
ID: 18051581
teraplane,  insert / update will only say how many number of rows were affected. more often users are looking for selecting values to a text file based on some suggestion.  and hence my suggestion.  

and  if the requirement is to run it as a scheduled task (in your instance you are presuming it to be alinux based system) whereas i only suggested a more generic sql syntax which is available as a pre-defined feature.

different ideas, no offence!!!
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question