Database Reference
In-Depth Information
Specifying the output column delimiter
In noninteractive mode,
mysql
separates output columns by tabs and there is no option
for specifying the output delimiter. To produce output that uses a different delimiter,
postprocess
mysql
output. Suppose that you want to create an output file for use by a
program that expects values to be separated by colon characters (
:
) rather than tabs.
Under Unix, you can convert tabs to arbitrary delimiters by using a utility such as
tr
or
sed
. Any of the following commands change tabs to colons (
TAB
indicates where you
type a tab character):
%
mysql cookbook <
inputfile
| sed -e "s/
TAB
/:/g" >
outputfile
%
mysql cookbook <
inputfile
| tr "
TAB
" ":" >
outputfile
%
mysql cookbook <
inputfile
| tr "\011" ":" >
outputfile
The syntax differs among versions of
tr
; consult your local documentation. Also, some
shells use the tab character for special purposes such as filename completion. For such
shells, type a literal tab into the command by preceding it with Ctrl-V.
sed
is more powerful than
tr
because it understands regular expressions and permits
multiple substitutions. This is useful for producing output in something like comma-
separated values (CSV) format, which requires three substitutions:
1. Escape any quote characters that appear in the data by doubling them, so that when
you use the resulting CSV file, they won't be interpreted as column delimiters.
2. Change the tabs to commas.
3. Surround column values with quotes.
sed
permits all three substitutions to be performed in a single command line:
%
mysql cookbook <
inputfile
\
| sed -e 's/"/""/g' -e 's/
TAB
/","/g' -e 's/^/"/' -e 's/$/"/' >
outputfile
That's cryptic, to say the least. You can achieve the same result with other languages that
may be easier to read. Here's a short Perl script that does the same thing as the
sed
command (it converts tab-delimited input to CSV output), and includes comments to
document how it works:
#!/usr/bin/perl
# csv.pl: convert tab-delimited input to comma-separated values output
while
(
<>
)
# read next input line
{
s/"/""/g
;
# double quotes within column values
s/\t/","/g
;
# put "," between column values
s/^/"/
;
# add " before the first value
s/$/"/
;
# add " after the last value
print
;
# print the result
}
If you name the script
csv.pl
, use it like this:
