Using MySQL Queries to Dump/Format Data to Delimited Files

It is often use­ful to uti­lize MySQL queries, to export data from tables then write this data into files. MySQL offers the abil­i­ty to cus­tomize many of the details of the­se exports, which should cov­er most sit­u­a­tions.

In this exam­ple, we are select­ing data from a table, apply­ing some for­mat­ting and writ­ing it to a file.

SELECT code, name, REPLACE(REPLACE(REPLACE(comment,"\r\n\r\n",'||'),"\r\n",'||'),"\n",'||') 
INTO OUTFILE '/tmp/20111222_practice_comments.csv' 
FIELDS TERMINATED BY ','  
OPTIONALLY ENCLOSED BY '"' 
ESCAPED BY '\\' 
LINES TERMINATED BY '\n' 
FROM practice_table;

Breakdown of the meaning of each line in this query example

select code, name, replace(replace(replace(comment,"\r\n\r\n",'||'),"\r\n",'||'),"\n",'||')

We are select­ing 3 fields: code, name and com­ment; on the com­ment field, we are replac­ing new­li­nes, (assum­ing win­dows new­li­nes in this sit­u­a­tion). The new­li­nes are replaced with two pipe char­ac­ters (i.e. ||).

into outfile '/tmp/20111222_practice_comments.csv'

This spec­i­fies the file into which we are writ­ing. This will write to your cur­rent direc­to­ry unless you spec­i­fy a full path name. Make sure the file does not already exist, or you will get an error. 

fields terminated by ','

You can change the field delim­iter in this line. We are using a com­ma here.

optionally enclosed by '"'

The option­al­ly enclosed by option tells what the sys­tem that we want to enclose each column by quotes.

escaped by '\\'

In the event that a quote appears in the field, it will be escaped by a back­slash.

lines terminated by '\n'

After each row, the sys­tem will place a new­line char­ac­ter. This line ter­mi­nat­ed by direc­tive can be changed to any char­ac­ter of your choos­ing.

from practice_table;

After all of the above con­fig­u­ra­tion, you place the FROM and pos­si­bly any WHERE clause.

1 thought on “Using MySQL Queries to Dump/Format Data to Delimited Files”

Leave a Reply

Your email address will not be published. Required fields are marked *