MySQL – Export Table into CSV File
Table of content
- Export MySQL Table into CSV File
- Storage Location of Exported .csv File
- Exporting MySQL Data in CSV Format
- Exporting Table Data Along with Column Headings
- Exporting Table Data Without Specifying Column Names
- Client Program
”;
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 ''rn'';
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:ProgramDataMySQLMySQL Server 8.0Uploads |
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 ''rn'';
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 ''rn'';
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 ''rn'';
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 ''rn'';
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 ''rn'';;
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 ''rn'';
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 ''rn'';
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
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 ''rn''"; $mysqli->query($sql);
To export the table into the CSV file through a JavaScript program, we have to pass the source file path and need to execute the “SELECT” statement using the query() function of mysql2 library as follows −
sql = `SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new1.csv'' FIELDS ENCLOSED BY ''"'' TERMINATED BY ''; '' ESCAPED BY ''"'' LINES TERMINATED BY ''rn''`; con.query(sql);
To export the table into the CSV file through a Java program, we have to pass the source file path and need to execute the “SELECT” statement using the JDBC function execute() as follows −
String 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''"; statement.execute(sql);
To export the table into the CSV file through a Python program, we have to pass the source file path and need to execute the “SELECT” statement using the execute() function of the MySQL Connector/Python as follows −
sql = "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 ''rn''" cursorObj.execute(sql)
Example
Following are the programs −
$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 ''rn''"; 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
var mysql = require(''mysql2''); var con = mysql.createConnection({ host:"localhost", user:"root", password:"password" }); //Connecting to MySQL con.connect(function(err) { if (err) throw err; // console.log("Connected successfully...!"); // console.log("--------------------------"); sql = "USE TUTORIALS"; con.query(sql); sql = `SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new1.csv'' FIELDS ENCLOSED BY ''"'' TERMINATED BY '';'' ESCAPED BY ''"'' LINES TERMINATED BY ''rn''`; con.query(sql, function(err, result){ console.log("Table data exported successfully...!"); console.log("Data: ") if (err) throw err; console.log(result); }); });
Output
The output obtained is as shown below −
Table data exported successfully...! Data: ResultSetHeader { fieldCount: 0, affectedRows: 7, insertId: 0, info: '''', serverStatus: 34, warningStatus: 0, changedRows: 0 }
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class ExportTableToCSVFile { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String user = "root"; String password = "password"; ResultSet rs; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection con = DriverManager.getConnection(url, user, password); Statement st = con.createStatement(); //System.out.println("Database connected successfully...!"); String 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''"; st.execute(sql); System.out.println("Successfully...! table exported into CSV file.."); }catch(Exception e) { e.printStackTrace(); } } }
Output
The output obtained is as shown below −
Successfully...! table exported into CSV file..
import mysql.connector #establishing the connection connection = mysql.connector.connect( host=''localhost'', user=''root'', password=''password'', database=''tut'' ) cursorObj = connection.cursor() # Query to export table into csv file sql = """ 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 ''rn''; """ cursorObj.execute(sql) print("Table data expoted successfully") # Reading and displaying the exported CSV file with open(''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'', ''r'') as csvfile: for row in csvfile: # Use strip() to remove extra newlines print(row.strip()) # Closing the cursor and connection cursorObj.close() connection.close()
Output
The output obtained is as shown below −
Table data expoted successfully "1";"Ramesh";"32";"Ahmedabad";"2000.00" "2";"Khilan";"25";"Delhi";"1500.00" "3";"kaushik";"23";"Kota";"2000.00" "4";"Chaitali";"25";"Mumbai";"6500.00" "5";"Hardik";"27";"Bhopal";"8500.00" "6";"Komal";"22";"MP";"4500.00" "7";"Muffy";"24";"Indore";"10000.00"