Importing and Exporting Data - MySQL - page 406

in tab-delimited, linefeed-terminated format, so first use cvt_file.pl to convert the input
to tab-delimited, linefeed-terminated format, and cvt_date.pl to convert the dates:
% cvt_file.pl --iformat=csv commodities.csv > tmp1.txt
% cvt_date.pl --iformat=us tmp1.txt > tmp2.txt
Feed the resulting file, tmp2.txt , to guess_table.pl :
% guess_table.pl --labels --table=commodities tmp2.txt > commodities.sql
The CREATE TABLE statement that guess_table.pl writes to commodities.sql looks like this:
CREATE TABLE ` commodities `
(
` commodity ` VARCHAR ( 5 ) NOT NULL ,
` trade_date ` DATE NOT NULL ,
` shares ` BIGINT UNSIGNED NOT NULL ,
` price ` DOUBLE UNSIGNED NOT NULL ,
` change ` DOUBLE NOT NULL
);
guess_table.pl produces that statement based on heuristics such as these:
• A column that contains only numeric values is assumed to be a BIGINT if no values
contain a decimal point, and DOUBLE otherwise.
• A numeric column that contains no negative values is likely to be UNSIGNED .
• If a column contains no empty values, guess_table.pl assumes that it's probably NOT
NULL .
• Columns that cannot be classified as numbers or dates are taken to be VARCHAR
columns, with a length equal to the longest value present in the column.
You might want to edit the CREATE TABLE statement that guess_table.pl produces, to make
modifications such as using smaller integer types, increasing the size of character fields,
changing VARCHAR to CHAR , adding indexes, or changing a column name that is a reserved
word in MySQL.
To create the table, use the statement produced by guess_table.pl :
% mysql cookbook < commodities.sql
Then load the datafile into the table (skipping the initial row of labels):
mysql> LOAD DATA LOCAL INFILE 'tmp2.txt' INTO TABLE commodities
-> IGNORE 1 LINES;
The resulting table contents after import look like this:
mysql> SELECT * FROM commodities;
+-----------+------------+---------+--------+--------+
| commodity | trade_date | shares | price | change |
+-----------+------------+---------+--------+--------+
| sugar | 2014-12-14 | 1000000 | 10.5 | -0.125 |