Generating a CSV file using MySQL

Generating a CSV file is a pretty common task when you're building a site that allows users to add data to the site database. They might register, or add comments to things, or anything really. Every so often someone will want to export that data to a different app, Excel for example. CSV is a simple solution.

Actually generating the CSV file on the other hand, while easy, is a boring task in PHP. You need to open the database connection, select the right data, fopen a file to save it or send headers to force a downlown, lloop through the selected data formatting it and outputing it somehow - lots of hassle. A much simpler approach is to get MySQL to do all the work for you.

The first thing to do is to create an SQL SELECT statement that grabs all the data you need in the CSV. A simple example would be something like:

SELECT `users`.`last_name`,

`users`.`first_name`,

`users`.`email_address`,

DATE_FORMAT(`users`.`created_date`, '%d/%m/%Y'),

IF (`users`.`registered`=1, 'Yes', 'No')

FROM `users`

WHERE `users`.`active` = 1 

ORDER BY `users`.`last_name` ASC, `users`.`first_name` ASC

Once you have a data set that works you need to tell MySQL to put the data into a file rather than sending it to the query buffer. This is done with the INTO syntax.

SELECT `users`.`last_name`,

`users`.`first_name`,

`users`.`email_address`,

DATE_FORMAT(`users`.`created_date`, '%d/%m/%Y'),

IF (`users`.`registered`=1, 'Yes', 'No')

INTO OUTFILE 'path/to/csv/users.csv'

FROM `users`

WHERE `users`.`active` = 1 

ORDER BY `users`.`last_name` ASC, `users`.`first_name` ASC

The added line "INTO OUTFILE 'path/to/csv/users.csv'" tells MySQL to save the data into a file. You need to be careful of two things: first that the path is correct, and second that the file doesn't already exist otherwise the query will fail.

This isn't going to generate a proper CSV file yet though. We also need to tell MySQL how to format the data. Fortunately there are a couple of simple functions of the SELECT query, FIELDS and LINES, that can do all the work.

SELECT `users`.`last_name`,

`users`.`first_name`,

`users`.`email_address`,

DATE_FORMAT(`users`.`created_date`, '%d/%m/%Y'),

IF (`users`.`registered`=1, 'Yes', 'No')

INTO OUTFILE 'path/to/csv/users.csv'

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'

LINES TERMINATED BY '\n'

FROM `users`

WHERE `users`.`active` = 1 

ORDER BY `users`.`last_name` ASC, `users`.`first_name` ASC

TERMINATED BY simply means 'put this character after the data or line. OPTIONALLY ENCLOSED BY tells MySQL to put quotation marks around any data that contains characters that could break the file such as new lines.

Lastly you may need to add a line for field names at the top. This can be achieved with a UNION.

SELECT 'Last Name','First Name','Email Address','Date','Registered'

UNION

SELECT `users`.`last_name`,

`users`.`first_name`,

`users`.`email_address`,

DATE_FORMAT(`users`.`created_date`, '%d/%m/%Y'),

IF (`users`.`registered`=1, 'Yes', 'No')

INTO OUTFILE 'path/to/csv/users.csv'

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'

LINES TERMINATED BY '\n'

FROM `users`

WHERE `users`.`active` = 1 

ORDER BY `users`.`last_name` ASC, `users`.`first_name` ASC

That's all there is to it.

Excel Note: A common problem is that Excel will refuse to open a CSV file is the first two characters in the file are 'id'. If your first column is called 'id' and you're including a line for field names you'll need to call it something else.