Using MySQL Queries to Dump/Format Data to Delimited Files

It is often useful to utilize MySQL queries, to export data from tables then write this data into files. MySQL offers the ability to customize many of the details of these exports, which should cover most situations.

In this example, we are selecting data from a table, applying some formatting and writing 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 selecting 3 fields: code, name and comment; on the comment field, we are replacing newlines, (assuming windows newlines in this situation). The newlines are replaced with two pipe characters (i.e. ||).

into outfile '/tmp/20111222_practice_comments.csv'

This specifies the file into which we are writing. This will write to your current directory unless you specify 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 delimiter in this line. We are using a comma here.

optionally enclosed by '"'

The optionally enclosed by option tells what the system 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 backslash.

lines terminated by '\n'

After each row, the system will place a newline character. This line terminated by directive can be changed to any character of your choosing.

from practice_table;

After all of the above configuration, you place the FROM and possibly any WHERE clause.

1 comment

Leave a comment

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