There are four practical ways to get MySQL data into Excel or a CSV file, and the right one depends on how often you need it and where the file should end up:
| Method | Best for | Where the file is created |
|---|---|---|
| MySQL Workbench result export | A one-off export of any query, no typing | Your own computer |
SELECT ... INTO OUTFILE | Large tables, scheduled server-side exports | The database server, in one allowed folder |
mysql command-line client in batch mode | Scripts and quick dumps from a terminal | Wherever you run the command |
| Python with pandas | Real .xlsx files, several sheets, formatting | Wherever the script runs |
If you want Excel to pull fresh data from MySQL every time you click Refresh, that is a live connection rather than an export; it is covered in MySQL with Excel: import database data into spreadsheets.
Sample table
CREATE TABLE students (
roll_no INT PRIMARY KEY,
name VARCHAR(40),
course VARCHAR(30),
city VARCHAR(20),
fee_paid DECIMAL(10,2),
remarks VARCHAR(60)
);
INSERT INTO students VALUES
(101,'Ananya Iyer','BCA','Chennai',45000.00,'EMI, 2nd instalment due'),
(102,'Harpreet Singh','B.Com','Chandigarh',60000.00,NULL),
(103,'Mohd. Faiz','BCA','Noida',45000.00,'Paid in full');
Note the comma inside the first remark and the NULL in the second row. Both cause trouble in careless exports.
Method 1: MySQL Workbench
- Run your query, for example
SELECT * FROM students;. - In the result grid toolbar, click the Export recordset to an external file icon.
- Choose a format. CSV is the safest choice for Excel; Workbench also offers JSON, HTML, XML, SQL inserts and an "Excel spreadsheet" (an XML-based format that Excel opens but may warn about).
- Save the file and open it in Excel.
For a whole table there is also the Table Data Export Wizard (right-click the table in the schema navigator). Workbench exports what the query returns, so filter and rename columns in SQL first instead of cleaning up in Excel afterwards.
Method 2: SELECT ... INTO OUTFILE
This makes the server write the file, which is fast for millions of rows. The server only writes into the folder named by secure_file_priv:
SHOW VARIABLES LIKE 'secure_file_priv';
On a default Windows install this is usually C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\; Linux packages commonly use /var/lib/mysql-files/. An empty value means any folder; NULL means exports are disabled. Writing anywhere else fails with ERROR 1290: The MySQL server is running with the --secure-file-priv option.
SELECT * FROM students
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/students.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';
Use forward slashes in the path even on Windows. The file it produced:
101,"Ananya Iyer","BCA","Chennai",45000.00,"EMI, 2nd instalment due"
102,"Harpreet Singh","B.Com","Chandigarh",60000.00,\N
103,"Mohd. Faiz","BCA","Noida",45000.00,"Paid in full"
Two things to fix before handing this to someone: there is no header row, and the NULL came out as \N, which Excel shows literally. A UNION ALL adds the header and IFNULL() replaces the NULL:
SELECT 'roll_no','name','course','city','fee_paid','remarks'
UNION ALL
SELECT roll_no, name, course, city, fee_paid, IFNULL(remarks, '')
FROM students
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/students_h.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';
"roll_no","name","course","city","fee_paid","remarks"
"101","Ananya Iyer","BCA","Chennai","45000.00","EMI, 2nd instalment due"
"102","Harpreet Singh","B.Com","Chandigarh","60000.00",""
"103","Mohd. Faiz","BCA","Noida","45000.00","Paid in full"
Because of the UNION, every column is now a string, so numbers are quoted too. Excel still reads "45000.00" as a number when it opens the CSV. Also, INTO OUTFILE never overwrites: running it again gives ERROR 1086: File ... already exists, so use a new name or delete the old file. You need the FILE privilege, and on a cloud database (Amazon RDS, Azure, Google Cloud SQL) you usually cannot use it at all, because you have no access to the server's disk.
Method 3: the mysql client in batch mode
This runs on your machine and writes the file locally, so it works with remote and cloud servers too:
mysql -h db.example.com -u report_user -p -B -e "SELECT roll_no, name, remarks FROM students" college > students.tsv
roll_no name remarks
101 Ananya Iyer EMI, 2nd instalment due
102 Harpreet Singh NULL
103 Mohd. Faiz Paid in full
-B (batch) prints tab-separated values with a header row. Excel opens .tsv files cleanly, and because the separator is a tab, commas inside values are harmless. NULL appears as the word NULL; wrap columns in IFNULL() if that matters.
Method 4: Python and pandas for a real .xlsx
# pip install pandas sqlalchemy pymysql openpyxl
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://report_user:secret@localhost/college")
df = pd.read_sql("SELECT roll_no, name, course, city, fee_paid FROM students", engine)
with pd.ExcelWriter("students.xlsx") as xl:
df.to_excel(xl, sheet_name="All students", index=False)
df.groupby("course", as_index=False)["fee_paid"].sum() \
.to_excel(xl, sheet_name="Fees by course", index=False)
The workbook gets two sheets: the raw list, and fees per course (BCA 90000.00, B.Com 60000.00). More on this route in MySQL with pandas and connecting Python with MySQL.
Problems people hit when opening the file in Excel
- Garbled Hindi or other non-English names. Excel does not always detect UTF-8 in a CSV you double-click. Open it through Data > From Text/CSV and choose "65001: Unicode (UTF-8)" as the file origin.
- Long numbers turned into 9.87654E+11, or leading zeros lost (phone numbers, PIN codes, Aadhaar-style IDs). Import through From Text/CSV and set that column's type to Text.
- Dates shown as text or swapped day/month. MySQL writes
2026-11-16; set the column type to Date during import. - Over 1,048,576 rows. That is Excel's sheet limit. Aggregate in SQL first or split the export.
The reverse direction, loading a CSV into a table, is covered in how to import CSV data into MySQL. If the goal is a backup rather than a report, use mysqldump instead of CSV.