Databases Reference
In-Depth Information
The clause
FIELDS TERMINATED BY ','
specifies the character that delimits the field
values in the text file. For example, if you have a file called
academics.colon_sv
with
values separated by colons, you can import it by specifying the colon as the field
terminator:
mysql>
LOAD DATA INFILE 'academics.colon_sv' INTO
-> TABLE details FIELDS TERMINATED BY ':';
Writing Data into Comma-Delimited Files
You can use the
SELECT INTO OUTFILE
statement to write out the result of a query into
a comma-separated values (CSV) file that can be opened by a spreadsheet or other
program.
Let's export the list of artists from our music database into a CSV file. The query used
to list all the artists is shown below:
mysql>
USE music;
Database changed
mysql>
SELECT artist_name, album_name FROM
-> artist, album WHERE artist.artist_id=album.artist_id;
+---------------------------+------------------------------------------+
| artist_name | album_name |
+---------------------------+------------------------------------------+
| New Order | Retro - John McCready FAN |
| New Order | Substance (Disc 2) |
| New Order | Retro - Miranda Sawyer POP |
| New Order | Retro - New Order / Bobby Gillespie LIVE |
| New Order | Power, Corruption & Lies |
| New Order | Substance 1987 (Disc 1) |
| New Order | Brotherhood |
| Nick Cave & The Bad Seeds | Let Love In |
| Miles Davis | Live Around The World |
| Miles Davis | In A Silent Way |
| The Rolling Stones | Exile On Main Street |
| The Stone Roses | Second Coming |
| Kylie Minogue | Light Years |
+---------------------------+------------------------------------------+
13 rows in set (0.10 sec)
We can change this
SELECT
query slightly to write this data into an output file as comma-
separated values:
mysql>
SELECT artist_name, album_name FROM
-> artist, album WHERE artist.artist_id=album.artist_id
-> INTO OUTFILE '/tmp/artists_and_albums.csv' FIELDS TERMINATED BY ',';
Query OK, 13 rows affected (0.02 sec)
Here, we've saved the results into the file
artists_and_albums.csv
in the
/tmp
directory;
the MySQL server must be able to write to the directory that you specify. On a Windows
system, specify a path such as
C:\artists_and_albums.csv
instead. If you omit the
FIELDS
