Specifying Arbitrary Output Column Delimiters

1.23.1 Problem

You want mysql to produce query output using a delimiter other than tab.

1.23.2 Solution

Postprocess mysql's output.

1.23.3 Discussion

In non-interactive mode, mysql separates output columns with tabs and there is no option for specifying the output delimiter. Under some circumstances, it may be desirable to produce output that uses a different delimiter. Suppose 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 utilities such as tr and sed. For example, to change tabs to colons, any of the following commands would work (TAB indicates where you type a tab character):[7]

[7] The syntax for some versions of tr may be different; 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.

% mysql cookbook <  inputfile   | sed -e "s/ TAB /:/g" >  outputfile 
% mysql cookbook <  inputfile   | tr " TAB " ":" >  outputfile 
% mysql cookbook <  inputfile   | tr "11" ":" >  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:

  • 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 taken as column delimiters.
  • Change the tabs to commas.
  • Surround column values with quotes.

sed allows all three subsitutions 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
while (<>) # read next input line
 s/"/""/g; # double any quotes within column values
 s/	/","/g; # put `","' between column values
 s/^/"/; # add `"' before the first value
 s/$/"/; # add `"' after the last value
 print; # print the result
exit (0);

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 may be necessary to invoke Perl explicitly:

C:> mysql cookbook <  inputfile   | perl csv.pl >  outputfile 

Perl may be more suitable if you need a cross-platform solution, because it runs under both Unix and Windows. tr and sed normally are unavailable under Windows.

1.23.4 See Also

An 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 more general-purpose file reformatter.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References

MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net