mysql

Khóa học miễn phí MySQL – Export Table into CSV File nhận dự án làm có lương

Posted on by user

MySQL – Export Table into CSV File

Table of content

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 −

Export table into csv

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.

Export table into csv2

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 −

Export table into csv3

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 −

Export table into csv4

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” −

Export table into csv5

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"

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

user