CODEWITHSUNDEEP
mysqlformattingbatch-processing
OutOfBoundsException
OutOfBoundsException

Reputation: 766

How to store MySQL results to a file in table format

I am trying to create a file and store in it the results from my query. I have a batch file that contains the single query,

USE database1;

SELECT * FROM table WHERE name = "abc" INTO OUTFILE output.txt;

QUIT

Executing this batch file using,

mysql -u root -p -t -vvv < select.sql

However, the result is not table formatted and fields' names are missing from the top.

100 abc Brown 32
101 abc Flair 25
102 abc McDonald 45
.
.
.

If I remove the INTO OUTFILE statement and print the results on terminal, then is working OK.

+----+------+---------+-----+
| id | name | surname | age |
+----+------+---------+-----+
| 100| abc  | Brown   |   32|
| 101| abc  | Flair   |   25|
| 102| abc  | McDonald|   45|
+----+------+---------+-----+

How can I achieve the above in a txt file?

UPDATE

Special thanks to GreyBeardedGeek. Here is the solution for this question with help of GreyBeardedGeek.

Batch file:

USE database1;

SELECT * FROM table WHERE name = "abc";

QUIT

and mysql client:

mysql -u root -p -t -vvv < select.sql > output.txt

Upvotes: 10

Views: 8667

Answers (3)

Robert Lujo
Robert Lujo

Reputation: 16361

Besides using mysql client program options (mysql --help) one can configure defaults within .my.cnf file. Example (MacOs, Linux) to enable --table option:

edit/create a file:

vim ~/.my.cnf

add/update:

[client]
table

next time you call mysql command line program it will use options from .my.cnf file producing output in table format, e.g. the same as one has called: 

mysql ... --table ...

Please note that command line options override options from the .my.cnf files.

References:

Upvotes: 0

Jeff Vanrykel
Jeff Vanrykel

Reputation: 1

You can also do following: 

mysql -uroot -p DatabaseName -t -e "select * from table" > file.txt

This doesn't need to make an SQL file and then filter out the first line.

Upvotes: 0

GreyBeardedGeek
GreyBeardedGeek

Reputation: 30088

This should do the trick:

mysql -u root -p -t -vvv < select.sql | sed '1 d' > output.txt

Upvotes: 5

Related Questions