CSV Data Export from Employees Table

Export Data to a CSV File Using SELECT INTO OUTFILE

Write a MySQL query to export all rows from the "Employees" table into a CSV file for backup purposes.

Solution:

-- This command exports all data from the Employees table to a CSV file
SELECT * FROM Employees
-- Specifies the output file location and name as /tmp/Employees_backup.csv
INTO OUTFILE '/tmp/Employees_backup.csv'
-- Sets the field separator to a comma (standard for CSV)
FIELDS TERMINATED BY ','
-- Encloses field values in double quotes for proper CSV formatting
ENCLOSED BY '"'
-- Sets the line separator to a newline character
LINES TERMINATED BY '\n';

Explanation:

• Purpose of the Query:
• To create a backup of table data in CSV format for easy portability.
• Demonstrates the SELECT INTO OUTFILE statement for exporting data.
• Key Components:
• FIELDS TERMINATED BY ',' : Defines the CSV field separator.
• ENCLOSED BY '"' : Encloses each field with quotes.
• LINES TERMINATED BY '\n' : Specifies the newline character as the row delimiter.
• Real-World Application:
• Useful for data migration and integration with other systems that support CSV.

Note:

• Ensure the MySQL server has permission to write to the specified directory.
• The output file must not already exist.

For more Practice: Solve these Related Problems:

• Write a SQL query to export all rows from the "Employees" table into a CSV file with a semicolon as the delimiter.
• Write a SQL query to export data from the "Orders" table to a CSV file, enclosing field values in single quotes.
• Write a SQL query to export selected columns from the "Products" table into a CSV file and include a header row.
• Write a SQL query to export data from the "Customers" table into a CSV file using UTF-8 encoding and a custom line terminator.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Backup Using mysqlpump for Parallel Processing.
Next MySQL Exercise: Import Data from a CSV File using LOAD DATA INFILE.