How to create customized CSV files directly from MySQL query results
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.
Configure Spring with Hibernate – Basics – Part 2