#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

MySQL with Excel: Import Database Data into Spreadsheets

Every Monday someone exports last week's orders from MySQL, emails a CSV, and five people paste it into their own copies of the same workbook. By Wednesday nobody agrees on the numbers. Connecting Excel directly to MySQL fixes this: the workbook holds a query, not a pasted copy, and one click on Refresh pulls the current data from the database.

This page covers the live connection. If you only need a one-time file, the methods in exporting MySQL data to Excel are simpler.

Step 1: prepare the database side

Do not point Excel at raw tables with an admin login. Give the workbook a small, stable view and a user that can read only that view. Here is a sample orders table and a monthly summary view:

CREATE TABLE orders (
  order_id   INT PRIMARY KEY,
  order_date DATE NOT NULL,
  city       VARCHAR(20) NOT NULL,
  product    VARCHAR(30) NOT NULL,
  qty        INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL
);

INSERT INTO orders VALUES
(1,'2026-09-03','Lucknow','USB keyboard',3,649.00),
(2,'2026-09-11','Indore','Webcam',1,1899.00),
(3,'2026-09-19','Lucknow','Webcam',2,1899.00),
(4,'2026-10-02','Kochi','USB keyboard',5,649.00),
(5,'2026-10-14','Indore','Monitor stand',2,1250.00),
(6,'2026-10-27','Lucknow','Monitor stand',1,1250.00),
(7,'2026-11-05','Kochi','Webcam',1,1899.00),
(8,'2026-11-09','Indore','USB keyboard',4,649.00);

CREATE VIEW v_monthly_city_sales AS
SELECT DATE_FORMAT(order_date,'%Y-%m') AS sale_month,
       city,
       SUM(qty)              AS units,
       SUM(qty * unit_price) AS revenue
FROM orders
GROUP BY sale_month, city;

SELECT * FROM v_monthly_city_sales ORDER BY sale_month, city;
sale_monthcityunitsrevenue
2026-09Indore11899.00
2026-09Lucknow55745.00
2026-10Indore22500.00
2026-10Kochi53245.00
2026-10Lucknow11250.00
2026-11Indore42596.00
2026-11Kochi11899.00

This is exactly what Excel will receive. Doing the grouping in MySQL means Excel downloads 7 summary rows instead of every order, which matters once the table has lakhs of rows. Now create the read-only login (the database here is called xl; use your own name and a strong password):

CREATE USER 'excel_reader'@'%' IDENTIFIED BY 'Ch4nge-Me-Now!';
GRANT SELECT ON xl.v_monthly_city_sales TO 'excel_reader'@'%';
SHOW GRANTS FOR 'excel_reader'@'%';
Grants for excel_reader@%
GRANT USAGE ON *.* TO `excel_reader`@`%`
GRANT SELECT ON `xl`.`v_monthly_city_sales` TO `excel_reader`@`%`

USAGE means "can log in, nothing else". The second line is the only real permission. Replace '%' with an office IP or subnet if you can. The guide to MySQL users and privileges explains host patterns in more detail.

Step 2: connect from Excel

Excel for Windows offers two routes. Menu names shift a little between Microsoft 365 builds, but the flow is the same.

Route A: ODBC (works in most Excel versions)

  1. Download and install MySQL Connector/ODBC from the official MySQL downloads site. Pick the build that matches Excel's bitness (64-bit Excel needs the 64-bit driver). Check it in Excel under File → Account → About Excel.
  2. Open ODBC Data Sources (64-bit) in Windows, add a DSN with the MySQL driver, and enter the server, port (3306 by default), excel_reader and the database name. Click Test.
  3. In Excel: Data → Get Data → From Other Sources → From ODBC, choose the DSN, then choose v_monthly_city_sales in the Navigator.

Route B: Excel's MySQL connector

Data → Get Data → From Database → From MySQL Database asks for the server and database directly. It depends on MySQL Connector/NET being installed. If the option is missing or complains about a provider, use Route A.

Either way you land in Power Query. Use Transform Data to rename columns, set revenue as a decimal type and filter months. Then click Close & Load to a table, or Close & Load To… → PivotTable Report.

Step 3: keep it fresh

Problems people actually hit

SymptomUsual cause and fix
"Data source name not found" or driver missingThe driver bitness does not match Excel's. Install the matching Connector/ODBC and create the DSN in the matching ODBC tool.
Access denied for userWrong password, or the user's host part does not match the PC's IP. Check SHOW GRANTS and the host in CREATE USER.
Can't connect to serverA firewall or a bind-address=127.0.0.1 setting on the server blocks remote connections. Ask the DBA. Do not open 3306 to the whole internet.
Load stops at about a million rowsOne worksheet holds at most 1,048,576 rows. Aggregate in a view, or load to the Data Model instead of a sheet.
Dates arrive as textSet the column type in Power Query, or return a real DATE column from the view, not a formatted string.
Colleague's refresh failsCredentials are saved per user, not inside the file. Each person enters the login once, under Data source settings.

When this is the wrong tool

Live Excel connections work well for a handful of people and summary-sized data. If many people need shared charts, scheduled refresh and access control, a BI tool fits better. See connecting MySQL with Power BI or connecting MySQL with Tableau. If you are still deciding how much work should move out of spreadsheets at all, read MySQL vs Excel for data analysis.

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 →