How to save MySQL query output to excel or .txt file?

Question

How do you save output of a MySQL query to a MS Excel sheet?

Even if it's only possible to store the data in a .txt file, it will be okay.

if you are using phpmyadmin, there is an option to export as csv or excel ect — Krish R, Jan 21 '14 at 09:21
- Goto PHPmyadmin in browser and choose database - Execute your sql query in the SQL tab in phpMyAdmin or select tables. - After execution / after select the table , scroll down the page and look for "Query results operations" - Click "Export" link and there is a page to export all the results you can select desired format — Krish R, Jan 21 '14 at 09:48
You can [Format Table Data as Text table](http://stackoverflow.com/a/34084279/1045444). — Somnath Muluk, Dec 04 '15 at 09:45

score 291 · Accepted Answer · edited May 05 '23 at 05:19

291

From Save MySQL query results into a text or CSV file:

MySQL provides an easy mechanism for writing the results of a select statement into a text file on the server. Using extended options of the INTO OUTFILE nomenclature, it is possible to create a comma separated value (CSV) which can be imported into a spreadsheet application such as OpenOffice or Excel or any other application which accepts data in CSV format.

Given a query such as
SELECT order_id,product_name,qty FROM orders
which returns three columns of data, the results can be placed into the file /tmp/orders.txt using the query:
SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.txt'
This will create a tab-separated file, each row on its own line. To alter this behavior, it is possible to add modifiers to the query:
SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
In this example, each field will be enclosed in double quotes, the fields will be separated by commas, and each row will be output on a new line separated by a newline (\n). Sample output of this command would look like:
"1","Tech-Recipes sock puppet","14.95" "2","Tech-Recipes chef's hat","18.95"
Keep in mind that the output file must not already exist and that the user MySQL is running as has write permissions to the directory MySQL is attempting to write the file to.

Syntax

   SELECT Your_Column_Name
    FROM Your_Table_Name
    INTO OUTFILE 'Filename.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'

Or you could try to grab the output via the client:

You could try executing the query from the your local client and redirect the output to a local file destination:

mysql -user -pass -e "select cols from table where cols not null" > /tmp/output

Hint: If you don't specify an absolute path but use something like INTO OUTFILE 'output.csv' or INTO OUTFILE './output.csv', it will store the output file to the directory specified by show variables like 'datadir';.

edited May 05 '23 at 05:19

Josh Correia

3,807
3
33
50

answered Jan 21 '14 at 09:19

Amarnath Balasubramanian

9,300
8
34
62

The MySQL username is not a system username, and thus cannot by definition have right access to anything, not even to /tmp. What is even the point of using the mysql userid instead of the effective userid to deduce writing permissions? Is there a way to bypass this (and I don't mean by creating a new system user). – Thalis K. Jan 28 '15 at 09:26
3

It's important to note that this create the file in the server. As the docs of select into explains, you'll have to use the mysql command as mysql -e "select ...". http://dev.mysql.com/doc/refman/5.7/en/select-into.html – jgomo3 Feb 04 '16 at 21:57
26

Just a note to say that if you get this error: `ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement`, you can stillsave the file in `/var/lib/mysql-files/` (Debian) and move it to your desired location after. – maulinglawns Jan 20 '17 at 17:31
5

This does not work in most cases where you are a client and the file is being created on the server machine that the client does not have access any way. I voted down this answer. – Kemin Zhou Aug 25 '17 at 00:55
If the server is running under SELinux in enforcing mode, the file might appear to write but when you look, the file's not there. To get around this you can specify a file path where the server is allowed to write, e.g. '/var/lib/mysql/output.txt'. I hope it won't let you overwrite anything important, but be careful with the file names. – Dan Pritts Oct 25 '19 at 18:57
On shared hosting, this might work but accessing that file is damn near impossible – TheRealChx101 Dec 15 '21 at 08:38
`mysql -e statement` is short-hand for `mysql --execute=statement` – osullic Apr 04 '22 at 15:15
I got this: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement Finally, I used https://tableconvert.com/mysql-to-excel to convert mysql query output to excel format – Raos Jan 11 '23 at 03:36

score 13 · Answer 2 · edited Feb 02 '18 at 04:50

13

You can write following codes to achieve this task:

SELECT ... FROM ... WHERE ... 
INTO OUTFILE 'textfile.csv'
FIELDS TERMINATED BY '|'

It export the result to CSV and then export it to excel sheet.

edited Feb 02 '18 at 04:50

codeforester

39,467
16
112
140

answered Jan 21 '14 at 09:18

Charles Stevens

1,568
15
30

9

got error code: 1045 Access denied – user2568374 Jul 28 '17 at 14:17
2

@user2568374 your mysql user do not have permission to write inside folder you specified. you can run this directly from command line as: mysql -u USER -pPASSWORD -e "select ... from database.table where ..." > desired/file.txt – mandza Feb 02 '18 at 14:14
1

Can't create/write to file, Permission denied !!! (I'm root). Updates: I knew how to fix the error, we need to give the folder's permission 1777, example : chmod 1777 /var/lib/mysql-files/ – Dylan B Nov 03 '18 at 07:29

How to save MySQL query output to excel or .txt file?

2 Answers2

Linked