#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Analytics

How to Export Data from MySQL to Excel and CSV

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:

MethodBest forWhere the file is created
MySQL Workbench result exportA one-off export of any query, no typingYour own computer
SELECT ... INTO OUTFILELarge tables, scheduled server-side exportsThe database server, in one allowed folder
mysql command-line client in batch modeScripts and quick dumps from a terminalWherever you run the command
Python with pandasReal .xlsx files, several sheets, formattingWherever 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

  1. Run your query, for example SELECT * FROM students;.
  2. In the result grid toolbar, click the Export recordset to an external file icon.
  3. 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).
  4. 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

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.

Become a Job-Ready Data Analyst

Master MySQL, Advanced Excel, Power BI, Tableau and Python with Uncodemy's Data Analytics Training in Noida — live projects, expert mentors and Dedicated Placement Support.

Enroll in Data Analytics Course →