Solved

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

Posted on 2006-11-29
4
471 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 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

705 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