Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

Export Table into CSV File

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

Export Table into CSV File
MySQL is an open-source relational database management system that allows us to store and manage large volume of data. One of its key feature is to export data from a table into various formats and CSV is one of it. CSV stands for "Comma Separated Values" file.
﻿
This allows users to extract data from a table in a structured format that can be easily manipulated and analysed using other tools such as Microsoft Excel, Google documents, open office etc.
﻿
Export MySQL Table into CSV File
To export the MySQL table data into a CSV file, we can use the MySQL "SELECT INTO ... OUTFILE" statement. Before exporting any table data into CSV files in the database server, we must ensure the following things 
The MySQL server's process must have the read/write privileges to the specified target folder, where CSV file will be created.
The specified CSV file should be already present in the system (No duplicate file).
The exported CSV file can contain data from one or more tables, and it can be modified to include only particular columns or rows.
Syntax
Following is the syntax of SELECT INTO ... OUTFILE statement 
SELECT column_name1, column_name2,...
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/file_name.csv'     
FIELDS TERMINATED BY ','    
OPTIONALLY ENCLOSED BY '"'    
LINES TERMINATED BY '\r\n';
Where,
INTO OUTFILE is the path and name of the CSV file that we want to export the table data to.
FIELDS TERMINATED BY is the delimiter that separates the fields in the exported CSV file.
LINES TERMINATED is the line terminator character for exported CSV file.
Storage Location of Exported .csv File
In MySQL, when you export a file, such as a .csv file, the default storage location for the exported file is determined by the "secure_file_priv" variable.
To find out the default path for exported files, you can use the following SQL query 
SHOW VARIABLES LIKE "secure_file_priv";
We get the following output 
Variable_name
Value
secure_file_priv
C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\
Before exporting data to a .csv file, you will need to have at least one table in your MySQL database. Let us create a table named "CUSTOMERS" using the following SQL query 
CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);
Now, we are inserting data into the above created table as shown below 
INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, NULL, 2000.00 ),
(4, 'Chaitali', NULL, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, NULL, 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );
The CUSTOMERS table obtained is as shown below 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
NULL
2000.00
4
Chaitali
NULL
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
NULL
4500.00
7
Muffy
24
Indore
10000.00
Exporting MySQL Data in CSV Format
You can export MySQL data in CSV file using the SELECT INTO ... OUTFILE statement. Here, we are exporting the data of CUSTOMERS table into a CSV file named "CUSTOMERS_BACKUP" using the following query 
SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';
After executing the above query, the CSV format file will be created at the specified path. Following is the output obtained after executing the above query 
Query OK, 7 rows affected (0.01 sec)
Following is the image of "CUSTOMERS_BACKUP.csv" file when we opened it 
﻿
Handling File Already Exists Error 
If you attempt to export data into a file that already exists, MySQL will generate an error 
SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';
Following is the error obtained 
ERROR 1086 (HY000): File 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv' already exists
To avoid this error, you can either choose a different filename or delete the existing file before executing the export query.
Removing Enclosing Quotes for Numeric Records 
By default, all records in the CSV file will be enclosed in double quotes, including numeric values. If you want to remove the quotes for numeric records, you can use the OPTIONALLY clause before the ENCLOSED BY clause, as shown below 
SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';
Following is the output obtained after executing the above query 
Query OK, 7 rows affected (0.00 sec)
As we can see the image of CSV file below, the double quotes ("") are removed for the numeric records.
﻿
Exporting Table Data Along with Column Headings
To export table data along with their respective column headings, you can use the UNION ALL statement. This allows you to create a row with column names and then append the data rows. Here is an example query 
SELECT 'ID', 'NAME', 'EMAIL', 'PHONE', 'CITY'
UNION ALL
SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';
Output
The output obtained is as follows 
Query OK, 8 rows affected (0.01 sec)
Verification
If we verify the .csv file, we can see that the respective column names are added 
﻿
Exporting Table Data Without Specifying Column Names
You can also export table data into a CSV file without specifying column names.
Syntax
Following is the syntax to export table data into a CSV file without specifying column names 
TABLE table_name ORDER BY column_name LIMIT 100  
INTO OUTFILE '/path/filename.txt'  
FIELDS ENCLOSED BY '"'   
TERMINATED BY ';'   
ESCAPED BY '"'   
LINES TERMINATED BY '\r\n';;  
Example
In the following query, we are exporting the CUSTOMERS table data into "CUSTOMERS_BACKUP.csv" file without specifying their column names 
TABLE CUSTOMERS ORDER BY NAME LIMIT 100
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';
Output
The result produced is as follows 
Query OK, 7 rows affected (0.01 sec)
Verification
As we can see in the "CUSTOMERS_BACKUP.csv" file, the table data got exported 
﻿
Replacing NULL Values
If your table contains NULL values, you can use the IFNULL() function to replace them with specific values before exporting the data to a CSV file.
Example
In the following query, the IFNULL() function is used to replace NULL values in the "ADDRESS" column with "NULL_VALUE" before exporting the data as shown below 
SELECT ID, NAME, AGE, IFNULL(ADDRESS, 'NULL_VALUE') FROM CUSTOMERS
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';
Output
The result obtained is as shown below 
Query OK, 7 rows affected (0.00 sec)
Verification
The NULL values (N) are replaced with "NULL_VALUE" 
﻿
Using Client Program
We can also export table into CSV file using Client Program.
Syntax
PHPNodeJSJavaPython
To export the table into the CSV file through a PHP program, we have to pass the source file path and need to execute the "SELECT" statement using the mysqli function query() as follows 
$sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new.csv' FIELDS ENCLOSED BY '' TERMINATED BY ';
' ESCAPED BY '' LINES TERMINATED BY '\r\n'";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.');
$sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new.csv' FIELDS ENCLOSED BY '' TERMINATED BY ';' ESCAPED BY '' LINES TERMINATED BY '\r\n'";
if($result = $mysqli->query($sql)){
   printf("Table data exported successfully....!\n");
   print_r($result);
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as shown below 
Table data exported successfully....!
1     
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	NULL	2000.00
4	Chaitali	NULL	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	NULL	4500.00
7	Muffy	24	Indore	10000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Export Table into CSV File

Export MySQL Table into CSV File

Syntax

Storage Location of Exported .csv File

Exporting MySQL Data in CSV Format

Exporting Table Data Along with Column Headings

Output

Verification

Exporting Table Data Without Specifying Column Names

Syntax

Example

Output

Verification

Replacing NULL Values

Example

Output

Verification

Using Client Program

Syntax

Example

Output