[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"