Solved

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

Posted on 2006-11-29
4
461 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
  • 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 50 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Preface In the first article: A Better Website Login System (http://www.experts-exchange.com/A_2902.html) I introduced the EE Collaborative Login System and its intended purpose. In this article I will discuss some of the design consideratio…
I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

932 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now