How to create customized CSV files directly from MySQL query results

June 25, 2013 Bruno Database, 0

In one of my previous articles, I described how to import data from CSV file into MySQL and now I’ll show you how to export it into the CSV file directly, as a result of query execution.

First, let’s start with the basics. Here is the syntax for creating CSV file with some optional parameters

SELECT user_id, username
  FROM users
  INTO OUTFILE 'c:/users.csv'
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\n';

If you execute something like this, you’ll get the data in CSV organized in two columns. Now we want to customize it a little bit, adding some custom header. Let’s say we want to create a CSV file containing all users which logged in last time before “2013-01-01”. Our header will be something like “Users who think our application doesn’t exist anymore”.

We will achieve that with union. One thing about union, it will combine two result sets, so you need to have same number of columns. Our header is like one column, and the rest is organized in two columns. You can’t do something like this

SELECT 'Users who think our application doesn\'t exist anymore'
 UNION
SELECT user_id,
       username
  FROM users
--     here goes the WHERE magic that will help us select our forgetful users
INTO OUTFILE 'c:/users.csv'
  FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n';

If you try it, you’ll get the message: The used SELECT statements have a different number of columns.

To see that union works, we could just remove the user_id from our statement.

SELECT 'Users who think our application doesn\'t exist anymore'
 UNION
SELECT username
  FROM users
INTO OUTFILE 'c:/users.csv'
  FIELDS TERMINATED BY ';'
  LINES TERMINATED BY '\n';

Now, we could use MySQL concat() function to put the user_id and username as one string (to match the number of columns), but in that case, we would miss the separator. That’s why we will use concat_ws().

CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments.MySQL Reference Manual

So our query finally looks like this:

--     Header
SELECT 'Users who think our application doesn\'t exist anymore'
 UNION
--     Empty string, to make some space between header and the rest
SELECT ''
 UNION
--     Column names, separated by semicolon
SELECT concat_ws(';', 'User ID', 'User name')
 UNION
--     Valuable information
SELECT concat_ws(';', user_id, username)
  FROM users
--     here goes the WHERE magic that will help us select our forgetful users
INTO OUTFILE 'c:/users.csv'
  LINES TERMINATED BY '\n';

If you open the file in the MS Excel, you’ll see something like this:

You can see that the fields are separated correctly. Cell B7 is holding one username, but cell B2 is empty.

I know this is not a lot of customizations but it can help you if you want to put some headers, separate data, help you get the bigger picture etc.

Note: If the file users.csv exists in the specified location, you’ll get an error. With this you can only create it for the first time, but can not overwrite it.

csv, exporting, mysql

Configure Spring with Hibernate – Basics – Part 2