Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to save MySQL query output to excel or .txt file?
To save MySQL query output into a text file, we can use the OUTFILE command.
Let us first create a table.
mysql> create table SaveintoTextFile -> ( -> id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.55 sec)
Inserting records into the table.
mysql> insert into SaveintoTextFile values(1,'John'); Query OK, 1 row affected (0.44 sec) mysql> insert into SaveintoTextFile values(101,'Carol'); Query OK, 1 row affected (0.17 sec) mysql> insert into SaveintoTextFile values(3,'David'); Query OK, 1 row affected (0.14 sec
To display all the records.
mysql> select *from SaveintoTextFile;
Here is the output.
+------+-------+ | id | name | +------+-------+ | 1 | John | | 101 | Carol | | 3 | David | +------+-------+ 3 rows in set (0.00 sec)
The following is the syntax to display the result in a text file.
SELECT col_name1,col_name2,.......N from yourTableName INTO OUTFILE "Path of my.cnf file- secure-file-priv ";
Let us implement the above syntax in the following query. Here, our text file is “QueryOutput.txt”
mysql> SELECT id,name from SaveintoTextFile
-> INTO OUTFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/QueryOutput.txt";
Query OK, 3 rows affected (0.02 sec)
To check if the text file is created or not, check the “Uploads” folder.
Now, open the text file. The text would be visible in the file as shown in the following screenshot.
Updated on: 30-Jul-2019
3K+ Views
Advertisements