Loading Data from Comma-Delimited Files | 283

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 inset 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

Download 4.24 Mb.

Share with your friends: