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.

Arjun Thakur

Published on 21-Nov-2018 08:11:33

Previous Page Print Page Next Page