ProblemYou want mysql to produce statement output using a delimiter other than tabs. Solutionmysql itself offers no capability for setting the output delimiter, but you can postprocess mysql output to reformat it. DiscussionIn noninteractive mode, mysql separates output columns by tabs and there is no option for specifying the output delimiter. Under some circumstances, it might be desirable to produce output that uses a different delimiter. 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. For example, to change tabs to colons, any of the following commands would work (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 sed is more powerful than tr because it understands regular expressions and allows multiple substitutions. This is useful when you want to produce output in something like comma-separated values (CSV) format, which requires three substitutions:
sed allows all three substitutions to be performed in a single command: % mysql cookbook < inputfile \ | sed -e 's/"/""/g' -e 's/ TAB /","/g' -e 's/^/"/' -e 's/$/"/' > outputfile That's fairly 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 -w # csv.pl - convert tab-delimited input to comma-separated values output while (<>) # read next input line { s/"/""/g; # double any 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, you can use it like this: % mysql cookbook < inputfile | csv.pl > outputfile If you run the command under a version of Windows that doesn't know how to associate .pl files with Perl, it might be necessary to invoke Perl explicitly: C:\> mysql cookbook < inputfile | perl csv.pl > outputfile tr and sed normally are unavailable under Windows. If you need a cross-platform solution, Perl may be more suitable because it runs under both Unix and Windows. (On Unix systems, Perl usually is preinstalled. On Windows, it is freely available for you to install.) See AlsoAn even better way to produce CSV output is to use the Perl Text::CSV_XS module, which was designed for that purpose. This module is discussed in Chapter 10, where it's used to construct a general-purpose file reformatter. |