MySQL Cookbook, 2nd Edition

Converting Datafiles from One Format to Another

Problem

You want to convert a file to a different format to make it easier to work with, or so that another program can understand it.

Solution

Use the cvt_file.pl converter script described here.

Discussion

The mysql_to_text.pl script discussed in Writing Your Own Export Programs uses MySQL as a data source and produces output in the format you specify via the --delim, --quote, and --eol options. This section describes cvt_file.pl, a utility that provides similar formatting options, but for both input and output. It reads data from a file rather than from MySQL, and converts it from one format to another. For example, to read a tab-delimited file data.txt, convert it to colon-delimited format, and write the result to tmp.txt, invoke cvt_file.pl like this:

%cvt_file.pl --idelim="\t" --odelim=":" data.txt > tmp.txt

The cvt_file.pl script has separate options for input and output. Thus, whereas mysql_to_text.pl has just a --delim option for specifying the column delimiter, cvt_file.pl has separate --idelim and --odelim options to set the input and output line column delimiters. But as a shortcut, --delim is also supported; it sets the delimiter for both input and output. The full set of options that cvt_file.pl understands is as follows:

--idelim=str, --odelim=str, --delim=str

Set the column delimiter for input, output, or both. The option value can consist of one or more characters.

--iquote=c, --oquote=c, --quote=c

Set the column quote character ...