What's new

Welcome to GloTorrents Community

Join us now to get access to all our features. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, and so, so much more. It's also quick and totally free, so what are you waiting for?

Ask question

Ask Questions and Get Answers from Our Community

Answer

Answer Questions and Become an Expert on Your Topic

Contact Staff

Our Experts are Ready to Answer your Questions

MySQL Export Table to CSV

_.:=iTake=:._

Administrator
Staff member
ZeuS
Super Moderator
+Lifetime VIP+
Registered
Joined
Oct 20, 2018
Messages
1,370
Reaction score
1,431
Points
113
Credits
14,854
MySQL Export Table to CSV

Summary: in this tutorial, you will learn various techniques of how to export a MySQL table to a CSV file.
The CSV stands for comma separated values. You often use the CSV file format to exchange data between applications such as Microsoft Excel, Open Office, Google Docs, etc.
It will be useful to have data from MySQL database in CSV file format because you can analyze and format the data in the way you want.
MySQL provides an easy way to export the query’s result into a CSV file that resides in the database server.
Before exporting data, you must ensure that:
  • The MySQL server’s process has the write access to the target folder that contains the target CSV file.
  • The target CSV file must not exist.
The following query selects cancelled orders from the orders table:

Code:
SELECT

    orderNumber, status, orderDate, requiredDate, comments

FROM

    orders

WHERE

    status = 'Cancelled';

To export this result set into a CSV file, you add some clauses to the query above as follows:

Code:
SELECT

    orderNumber, status, orderDate, requiredDate, comments

FROM

    orders

WHERE

    status = 'Cancelled'

INTO OUTFILE 'C:/tmp/cancelled_orders.csv'

FIELDS ENCLOSED BY '"'

TERMINATED BY ';'

ESCAPED BY '"'

LINES TERMINATED BY '\r\n';

The statement created a CSV file named cancelled_orders.csv in the C:\tmp folder that contains the result set.
The CSV file contains lines of rows in the result set. Each line is terminated by a sequence of carriage return and a line feed character specified by the LINES TERMINATED BY '\r\n' clause. Each line contains values of each column of the row in the result set.
Each value is enclosed by double quotation marks indicated by FIELDS ENCLOSED BY '”' clause. This prevents the value that may contain a comma (,) will be interpreted as the field separator. When enclosing the values by the double quotation marks, the commas inside the value are not recognized as the field separators.

Exporting data to a CSV file whose filename contains timestamp

You often need to export data into a CSV file whose name contains timestamp at which the file is created. To do so, you need to use the MySQL prepared statement.
The following commands export the whole orders table into a CSV file with timestamp as a part of the file name.

Code:
SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');

SET @FOLDER = 'c:/tmp/';

SET @PREFIX = 'orders';

SET @EXT    = '.csv';


SET @CMD = CONCAT("SELECT * FROM orders INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,

    "' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"'",

    "  LINES TERMINATED BY '\r\n';");

PREPARE statement FROM @CMD;

EXECUTE statement;

Let’s examine the commands above in more detail.
  • First, we constructed a query with current timestamp as a part of the file name.
  • Second, we prepared the statement for execution by using PREPARE statement FROM command.
  • Third, we executed the statement by using the EXECUTE command.
You can wrap the command by an event and schedule the event run periodically if needed.

Exporting data with column headings

It would be convenient if the CSV file contains the first line as the column headings so that the file is more understandable.
To add the column headings, you need to use the UNION statement as follows:

Code:
(SELECT 'Order Number','Order Date','Status')

UNION

(SELECT orderNumber,orderDate, status

FROM orders

INTO OUTFILE 'C:/tmp/orders.csv'

FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'

LINES TERMINATED BY '\r\n');

As the query showed, you need to include the column heading of every column.

Handling NULL values
In case the values in the result set contain NULL values, the target file will contain "N instead of NULL. To fix this issue, you need to replace the NULL value by another value e.g., not applicable ( N/A ) by using the IFNULL function as the following query:

Code:
SELECT
    orderNumber, orderDate, IFNULL(shippedDate, 'N/A')
FROM
    orders INTO OUTFILE 'C:/tmp/orders2.csv'
    FIELDS ENCLOSED BY '"'
    TERMINATED BY ';'
    ESCAPED BY '"' LINES
    TERMINATED BY '\r\n';

We replaced NULL values in the shippedDate column by the N/A strings. The CSV file shows N/A instead of NULL values.

Exporting data to CSV file using MySQL Workbench
In case you don’t have access to the database server to get the exported CSV file, you can use MySQL Workbench to export the result set of a query to a CSV file in your local computer as follows:

  • First, execute a query get its result set.
  • Second, from the result panel, click “export recordset to an external file”. The result set is also known as a recordset.
  • Third, a new dialog displays. It asks you for a filename and file format. Enter the file name, choose CSV as the file format and click Save button.


The CSV file exported by MySQL Workbench supports column headings, NULL values and other great features.
 
Last edited:

Prom3th3uS

Administrator
Staff member
Junior Administrator
Super Moderator
+Lifetime VIP+
Contributor
Registered
Joined
Oct 21, 2018
Messages
836
Reaction score
321
Points
63
Location
Unknown
Credits
12,458
Very helpful tut. thanks buddy :)
 
shape1
shape2
shape3
shape4
shape7
shape8
Top