Importing and Exporting Data - MySQL - page 405

terize it as a VARCHAR column. If a datafile doesn't satisfy these assumptions, you may be
able to reformat it first using the cvt_file.pl and cvt_date.pl utilities described in Recipes
11.6 and 12.12 .
guess_table.pl understands the following options:
--labels
Interpret the first input line as a row of column labels and use them for table column
names. Without this option, guess_table.pl uses default column names of c1 , c2 ,
and so forth.
If the file contains a row of labels and you omit this option, guess_table.pl treats the
labels as data values. The likely result is that the script will characterize all columns
as VARCHAR columns (even those that otherwise contain only numeric or temporal
values), due to the presence of a nonnumeric or nontemporal value in the column.
--lower , --upper
Force column names in the CREATE TABLE statement to be lowercase or uppercase.
--quote-names , --skip-quote-names
Quote or do not quote table and column identifiers in the CREATE TABLE statement
with ` characters (for example, `mytbl` ). This can be useful if an identifier is a
reserved word. The default is to quote identifiers.
--report
Generate a report rather than a CREATE TABLE statement. The script displays the
information that it gathers about each column.
--table= tbl_name
Specify the table name to use in the CREATE TABLE statement. The default name is t .
Here's an example of how guess_table.pl works. Suppose that a file named commodi
ties.csv is in CSV format and has the following contents:
commodity,trade_date,shares,price,change
sugar,12-14-2014,1000000,10.50,-.125
oil,12-14-2014,96000,60.25,.25
wheat,12-14-2014,2500000,8.75,0
gold,12-14-2014,13000,103.25,2.25
sugar,12-15-2014,970000,10.60,.1
oil,12-15-2014,105000,60.5,.25
wheat,12-15-2014,2370000,8.65,-.1
gold,12-15-2014,11000,101,-2.25
The first row indicates the column labels, and the following rows contain data records,
one per line. The values in the trade_date column are dates, but they are in MM-DD-
CCYY format rather than the ISO format that MySQL expects. cvt_date.pl can convert
these dates to ISO format. However, both cvt_date.pl and guess_table.pl require input