MySQL – OUTFILE syntax


[adsense id=”0514458240″ width=”468″ height=”60″]

MySQL provides an extremely useful command which enables you to output the results of a query into a file. As with INFILE where the contents can be defined by field separator, line separators, fields enclosed by, the OUTFILE syntax also allows the same. The results allows contents to be exported easily and imported easily within MySQL but more than that we can generate comma separate values CSV files which can be imported into Excel spreadsheets.

This blog provides some basic information about how this is achieved through a simple example.

We have a table called countries which consists of an id, eutax field and the country name. We want to view only the countries within the European Union.

mysql> SELECT * FROM countries WHERE eutax = 1;
+----+-------+----------------+
| id | eutax | country        |
+----+-------+----------------+
|  1 |     1 | Austria        | 
|  2 |     1 | Belgium        | 
|  3 |     1 | Bulgaria       | 
|  4 |     1 | Cyprus         | 
|  5 |     1 | Czech Republic | 
|  6 |     1 | Denmark        | 
|  7 |     1 | Estonia        | 
|  8 |     1 | Finland        | 
|  9 |     1 | France         | 
| 10 |     1 | Germany        | 
| 11 |     1 | Greece         | 
| 12 |     1 | Hungary        | 
| 13 |     1 | Ireland        | 
| 14 |     1 | Italy          | 
| 15 |     1 | Latvia         | 
| 16 |     1 | Lithuania      | 
| 17 |     1 | Luxembourg     | 
| 18 |     1 | Malta          | 
| 19 |     1 | Monaco         | 
| 20 |     1 | Netherlands    | 
| 21 |     1 | Poland         | 
| 22 |     1 | Portugal       | 
| 23 |     1 | Romania        | 
| 24 |     1 | Slovakia       | 
| 25 |     1 | Slovenia       | 
| 26 |     1 | Spain          | 
| 27 |     1 | Sweden         | 
| 28 |     1 | United Kingdom | 
+----+-------+----------------+
28 rows in set (0.00 sec)

Now that we are sure this is the resultset we are searching for we now want to output this into a comma separated file:

When using the OUTFILE command it is imperative to specify a full pathname to the location where the file is to be output to and also that a file with that name does not already exist. If a file exists with the same name then you will receive the following error: “ERROR 1086 (HY000): File ‘filename‘ already exists”.

SELECT * FROM countries WHERE eutax = 1 INTO OUTFILE '/tmp/countries.txt';

Output from that file (that above uses the default behaviour i.e. TAB separated fields and no enclosure around the fields:

1       1       Austria
2       1       Belgium
3       1       Bulgaria
4       1       Cyprus
5       1       Czech Republic
6       1       Denmark
7       1       Estonia
8       1       Finland
9       1       France
10      1       Germany
11      1       Greece
12      1       Hungary
13      1       Ireland
14      1       Italy
15      1       Latvia
16      1       Lithuania
17      1       Luxembourg
18      1       Malta
19      1       Monaco
20      1       Netherlands
21      1       Poland
22      1       Portugal
23      1       Romania
24      1       Slovakia
25      1       Slovenia
26      1       Spain
27      1       Sweden
28      1       United Kingdom

If we want to add more including some styling i.e. enclosing each fields with a quote, separating each field with a comma we issue the following command:

SELECT id, country 
  FROM countries
 WHERE eutax = 1 
  INTO OUTFILE '/tmp/country.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

The resulting output will then look like:

"1","Austria"
"2","Belgium"
"3","Bulgaria"
"4","Cyprus"
"5","Czech Republic"
"6","Denmark"
"7","Estonia"
"8","Finland"
"9","France"
"10","Germany"
"11","Greece"
"12","Hungary"
"13","Ireland"
"14","Italy"
"15","Latvia"
"16","Lithuania"
"17","Luxembourg"
"18","Malta"
"19","Monaco"
"20","Netherlands"
"21","Poland"
"22","Portugal"
"23","Romania"
"24","Slovakia"
"25","Slovenia"
"26","Spain"
"27","Sweden"
"28","United Kingdom"