Categories
Database MySQL Snippet

Exporting MySQL Data to a .csv File

Sometimes you'll need to move data out of your database into a spreadsheet or other application to work with or to send to someone else.

The syntax below, not entirely common, will create a comma separated file at the disk location indicated. The example below refers to a windows system, adapt as appropriate for other operating systems.

Double-click on the file and it will open in Excel or any other application associated with the .csv extension.

SELECT lname,fname,address,email
INTO OUTFILE 'd:/CurrentCustomers.csv'
FIELDS
  TERMINATED BY ','
  ENCLOSED BY '"'
  ESCAPED BY '\\'
LINES
  TERMINATED BY '\n'
FROM customers
WHERE STATUS = 'current'
ORDER BY lname,fname;

A dynamic outfile name is another idea, as this statement will not overwrite an existing outfile.  An article on the  MySQL discussion board covers this, and while it does not provide a perfect answer, is points in the direction of a solution involving the PREPARE statement.