📥 MySQL Import / 📤 Export CSV – Complete Guide for Developers & DBAs

---

🧲 Introduction – Why Learn CSV Import and Export in MySQL?

Whether you’re migrating data, building dashboards, or integrating with external systems, CSV import/export is one of the most common tasks in MySQL. MySQL makes it easy to load CSV files into tables and export query results as CSV files, supporting a wide range of real-world scenarios like:

• 📊 Data migration from Excel
• 🔁 ETL pipelines and automation
• 📤 Backups and offline reports
• 🔄 Data synchronization between systems

🎯 In this tutorial, you’ll learn:

• How to import CSV to MySQL using LOAD DATA INFILE
• How to export MySQL query results to CSV
• GUI tools vs CLI methods
• Real-world use cases and best practices

---

📥 How to Import CSV File into MySQL

⚙️ Using LOAD DATA INFILE

LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE employees
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

🔍 Explanation:

Clause	Purpose
FIELDS TERMINATED BY ','	CSV separator (comma)
ENCLOSED BY '"'	Wrap values in double quotes
LINES TERMINATED BY '\n'	New row on new line
IGNORE 1 ROWS	Skip header row

💡 Tip:

• Ensure file is accessible by the MySQL server.
• Use LOCAL if uploading from client machine:

LOAD DATA LOCAL INFILE 'employees.csv' INTO TABLE employees ...

---

📘 Example Table & CSV

CSV File (employees.csv):

id,name,department,salary
1,Alice,HR,60000
2,Bob,Sales,70000
3,Charlie,IT,75000

Matching Table:

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  department VARCHAR(50),
  salary DECIMAL(10,2)
);

---

📤 How to Export Data from MySQL to CSV

🧪 Using INTO OUTFILE

SELECT id, name, department, salary
FROM employees
INTO OUTFILE '/var/lib/mysql-files/employees_export.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

🔍 Notes:

• File is written on the MySQL server, not on your client.
• The path must be writable by MySQL and in the secure-file-priv directory (check it via: SHOW VARIABLES LIKE 'secure_file_priv';).

---

🧰 MySQL Workbench Import / Export CSV

📥 Import CSV via MySQL Workbench

1. Open MySQL Workbench
2. Go to Table Data Import Wizard
3. Choose CSV file and target table
4. Map columns
5. Click Next → Import

📤 Export CSV via MySQL Workbench

1. Right-click the table → Table Data Export Wizard
2. Choose columns or use a custom SQL query
3. Select file path and format
4. Click Next → Export

---

💡 Best Practices

📘 Use Matching Column Order: Ensure your CSV column order aligns with the MySQL table.

📘 Data Sanitization: Clean the CSV file to avoid errors due to missing quotes or malformed lines.

📘 Use Transactions (Optional):

START TRANSACTION;
-- LOAD DATA INFILE ...
COMMIT;

📘 Secure Your File Paths: Avoid using unrestricted paths on shared servers.

⚠️ Pitfall: If secure_file_priv is set, you must place your file inside the permitted directory.

---

🚀 Real-World Use Cases

Use Case	How CSV Helps
📈 Excel Integration	Easily export to Excel-readable CSV files
🛠️ Bulk Data Upload	Insert thousands of rows at once from files
🔄 External Sync	Export MySQL data to sync with APIs or partners
📚 Database Migration	Move data between MySQL instances or other RDBMS

---

📌 Summary – Recap & Next Steps

Using CSV for import and export in MySQL is fast, efficient, and essential for any backend developer or DBA.

🔍 Key Takeaways:

• Use LOAD DATA INFILE for importing CSV to MySQL.
• Use SELECT ... INTO OUTFILE to export MySQL data to CSV.
• Tools like MySQL Workbench simplify the process.

⚙️ Real-World Relevance:
Essential for reporting, migrations, integration, and automation in any data-driven application.

---

❓ FAQ – MySQL CSV Import / Export

❓ How do I fix “The MySQL server is running with the –secure-file-priv option”?

✅ Run:

SHOW VARIABLES LIKE 'secure_file_priv';

Then place your file in that directory.

---

❓ Can I import CSV without a header row?

✅ Yes. Simply omit IGNORE 1 ROWS in your LOAD DATA INFILE query.

---

❓ Can I automate CSV import/export with cron jobs?

✅ Yes. Use CLI + .sql files + mysql or mysqldump commands in shell scripts.

---

❓ What are alternatives to CSV in MySQL?

✅ JSON, XML, and database dumps (mysqldump for .sql files).

---

❓ How to export with a WHERE clause?

✅ Example:

SELECT * 
FROM orders 
WHERE status = 'completed' 
INTO OUTFILE 'completed_orders.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

---

Share Now :