Exporting & Importing CSV Data0%

Project 1: Student Information System Database

Exporting & Importing CSV Data

Beginner12 min read•Updated: 2026-09-12

Exporting & Importing CSV Data: LOAD DATA INFILE & Workbench

In real-world data engineering, backend development, and business analytics, you will constantly exchange data with external business teams who work in Microsoft Excel or Google Sheets. Mastering how to rapidly bulk-import hundreds of thousands of spreadsheet rows using LOAD DATA INFILE and export query results to CSV files is an essential practical skill.

1. High-Speed Bulk Import: `LOAD DATA INFILE`

While inserting 100,000 rows via standard SQL INSERT statements can take minutes, MySQL's LOAD DATA INFILE engine can parse and ingest the same data in under two seconds!

1LOAD DATA LOCAL INFILE 'C:/data/students.csv'
2INTO TABLE students
3FIELDS TERMINATED BY ',' 
4ENCLOSED BY '"'
5LINES TERMINATED BY '\r\n'
6IGNORE 1 ROWS
7(first_name, last_name, email, fee_paid, enrollment_date);

Breaking Down the Clauses:

LOCAL: Instructs the MySQL client to read the file from your local machine and transmit it to the server.
FIELDS TERMINATED BY ',': Specifies the column delimiter (comma for CSV, \t for TSV).
ENCLOSED BY '"': Handles strings that contain commas inside quotes (e.g., "Flat 4, Nariman Point").
LINES TERMINATED BY '\r\n': Standard Windows line endings (\n on Linux/macOS).
IGNORE 1 ROWS: Skips the first header row containing column titles.
(column_list): Maps CSV fields to specific table columns.

2. Enabling `local_infile` Security in MySQL

Because reading local client files has security implications, MySQL disables LOCAL INFILE by default in modern installations. To enable it:

1-- Enable on server session:
2SET GLOBAL local_infile = 1;

And when connecting from the terminal, append the --local-infile=1 flag:

1mysql --local-infile=1 -u root -p msk_tech_academy

3. Exporting Query Results to CSV (`INTO OUTFILE`)

You can export any query directly to a comma-separated text file on the server using INTO OUTFILE:

1SELECT customer_id, full_name, email, account_balance
2FROM customers
3WHERE account_status = 'Active'
4INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/active_customers.csv'
5FIELDS TERMINATED BY ','
6ENCLOSED BY '"'
7LINES TERMINATED BY '\r\n';

Important Warning

[!WARNING] The secure_file_priv Restriction: For security, MySQL will refuse to write files to arbitrary directories like C:\ or /etc/. It restricts file exports to the directory configured in SHOW VARIABLES LIKE 'secure_file_priv';.

4. Visual CSV Import/Export via MySQL Workbench

If you prefer a point-and-click GUI workflow without security permissions tweaking:

1
Right-click any table in the Schemas Navigator -> Select Table Data Import Wizard.
2
Browse to your CSV file -> Click Next.
3
Map CSV columns to table columns using the visual dropdown interface.
4
Click Next to run the import.
5
To export: Execute any SELECT query -> Click the "Export" floppy disk icon in the Result Grid toolbar -> Choose CSV.

5. Best Practices & Common Pitfalls

Watch Out for Header Rows: Always specify IGNORE 1 ROWS when importing CSVs with header columns. Otherwise, the string "fee_paid" will attempt to insert into a DECIMAL column, throwing an error!
Line Ending Discrepancies: CSV files generated on Windows use Carriage Return + Line Feed (\r\n), while files generated on Linux/macOS use Line Feed (\n). Always check your file's line ending format.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which MySQL statement provides the fastest method for bulk-importing thousands of rows from a text or CSV file into a database table?

Question 2

What clause in LOAD DATA INFILE skips the first line of a CSV file containing column headers?

Question 3

Which server configuration variable restricts where files can be written to disk when using INTO OUTFILE?

Question 4

What does the ENCLOSED BY '"' clause accomplish during CSV import?

Question 5

Why is the LOCAL keyword used in LOAD DATA LOCAL INFILE?

Next Lesson

Project 1: Student Information System Database

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Restoring Databases & Running SQL Dump Scripts	Project 1: Student Information System Database

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Project 1: Student Information System Database

Exporting & Importing CSV Data

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Exporting & Importing CSV Data: LOAD DATA INFILE & Workbench

1. High-Speed Bulk Import: `LOAD DATA INFILE`

While inserting 100,000 rows via standard SQL INSERT statements can take minutes, MySQL's LOAD DATA INFILE engine can parse and ingest the same data in under two seconds!

1LOAD DATA LOCAL INFILE 'C:/data/students.csv'
2INTO TABLE students
3FIELDS TERMINATED BY ',' 
4ENCLOSED BY '"'
5LINES TERMINATED BY '\r\n'
6IGNORE 1 ROWS
7(first_name, last_name, email, fee_paid, enrollment_date);

Breaking Down the Clauses:

LOCAL: Instructs the MySQL client to read the file from your local machine and transmit it to the server.
FIELDS TERMINATED BY ',': Specifies the column delimiter (comma for CSV, \t for TSV).
ENCLOSED BY '"': Handles strings that contain commas inside quotes (e.g., "Flat 4, Nariman Point").
LINES TERMINATED BY '\r\n': Standard Windows line endings (\n on Linux/macOS).
IGNORE 1 ROWS: Skips the first header row containing column titles.
(column_list): Maps CSV fields to specific table columns.

2. Enabling `local_infile` Security in MySQL

Because reading local client files has security implications, MySQL disables LOCAL INFILE by default in modern installations. To enable it:

1-- Enable on server session:
2SET GLOBAL local_infile = 1;

And when connecting from the terminal, append the --local-infile=1 flag:

1mysql --local-infile=1 -u root -p msk_tech_academy

3. Exporting Query Results to CSV (`INTO OUTFILE`)

You can export any query directly to a comma-separated text file on the server using INTO OUTFILE:

1SELECT customer_id, full_name, email, account_balance
2FROM customers
3WHERE account_status = 'Active'
4INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/active_customers.csv'
5FIELDS TERMINATED BY ','
6ENCLOSED BY '"'
7LINES TERMINATED BY '\r\n';

Important Warning

4. Visual CSV Import/Export via MySQL Workbench

If you prefer a point-and-click GUI workflow without security permissions tweaking:

1
Right-click any table in the Schemas Navigator -> Select Table Data Import Wizard.
2
Browse to your CSV file -> Click Next.
3
Map CSV columns to table columns using the visual dropdown interface.
4
Click Next to run the import.
5
To export: Execute any SELECT query -> Click the "Export" floppy disk icon in the Result Grid toolbar -> Choose CSV.

5. Best Practices & Common Pitfalls

Watch Out for Header Rows: Always specify IGNORE 1 ROWS when importing CSVs with header columns. Otherwise, the string "fee_paid" will attempt to insert into a DECIMAL column, throwing an error!
Line Ending Discrepancies: CSV files generated on Windows use Carriage Return + Line Feed (\r\n), while files generated on Linux/macOS use Line Feed (\n). Always check your file's line ending format.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which MySQL statement provides the fastest method for bulk-importing thousands of rows from a text or CSV file into a database table?

Question 2

What clause in LOAD DATA INFILE skips the first line of a CSV file containing column headers?

Question 3

Which server configuration variable restricts where files can be written to disk when using INTO OUTFILE?

Question 4

What does the ENCLOSED BY '"' clause accomplish during CSV import?

Question 5

Why is the LOCAL keyword used in LOAD DATA LOCAL INFILE?

Next Lesson

Project 1: Student Information System Database

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Restoring Databases & Running SQL Dump Scripts	Project 1: Student Information System Database

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Exporting & Importing CSV Data: LOAD DATA INFILE & Workbench

1. High-Speed Bulk Import: LOAD DATA INFILE

Breaking Down the Clauses:

2. Enabling local_infile Security in MySQL

3. Exporting Query Results to CSV (INTO OUTFILE)

4. Visual CSV Import/Export via MySQL Workbench

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Project 1: Student Information System Database

Related Lessons

Practice Quiz

Loading Page Content...

Exporting & Importing CSV Data: LOAD DATA INFILE & Workbench

1. High-Speed Bulk Import: LOAD DATA INFILE

Breaking Down the Clauses:

2. Enabling local_infile Security in MySQL

3. Exporting Query Results to CSV (INTO OUTFILE)

4. Visual CSV Import/Export via MySQL Workbench

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Project 1: Student Information System Database

Related Lessons

Practice Quiz

1. High-Speed Bulk Import: `LOAD DATA INFILE`

2. Enabling `local_infile` Security in MySQL

3. Exporting Query Results to CSV (`INTO OUTFILE`)

1. High-Speed Bulk Import: `LOAD DATA INFILE`

2. Enabling `local_infile` Security in MySQL

3. Exporting Query Results to CSV (`INTO OUTFILE`)