MySQL Forums
Forum List  »  CSV Storage Engine

Excessive warning messages on import
Posted by: Craig Harrington
Date: October 21, 2008 11:14AM

I am drowning in warning messages. The situation: I am importing millions of rows of data in different tables. The data comes from an external source. The data is enclosed in double quotes and comma separated, consistent with RFC 4180. (There are samples below of the table and the data.) MySQL issues a warning whenever it encounters an empty field ("") that is being imported into a decimal column. I do not want to populate the empty fields with numeric values because they are actually empty and populating them will skew the information. Because the data has decimal fields on every row that are empty, the import process is filling up the hard drive with warning messages.

A typical warning message is "1366: Incorrect decimal value '' for column 'NBR_FIELD_1' at row 2", where there is a unique warning message for column of each row.

Is there a way to stop the warning messages? Is there another alternative that I'm overlooking?

(FYI, MySQL issues an error and stops if the empty field is being imported into an INTEGER column.)

Table Structure
================

Field Type Collation Null
------------------------- -------------- ----------------- ----
NBR_FIELD_1 decimal(9,0) NO
VCHAR_FIELD_1 varchar(30) latin1_swedish_ci YES
NBR_FIELD_2 decimal(6,0) YES
VCHAR_FIELD_2 varchar(30) latin1_swedish_ci YES
NBR_FIELD_3 decimal(9,0) YES
IS_VIRTUAL char(1) latin1_swedish_ci YES
VCHAR_FIELD_3 varchar(30) latin1_swedish_ci YES
NBR_FIELD_4 decimal(6,0) YES
VCHAR_FIELD_4 varchar(30) latin1_swedish_ci YES
ARE_CONTIGUOUS char(1) latin1_swedish_ci YES
ARE_MULTIPLES char(1) latin1_swedish_ci YES
IS_AGGREGATE char(1) latin1_swedish_ci YES
NBR_FIELD_5 decimal(12,0) YES
ALLOW_OVERSUBSCRIPTION char(1) latin1_swedish_ci YES
NBR_FIELD_6 decimal(12,2) YES
NBR_FIELD_7 decimal(18,0) YES
NBR_FIELD_8 decimal(18,0) YES
USE_IN_PATHS char(1) latin1_swedish_ci YES
USE_IN_NETWORK char(1) latin1_swedish_ci YES
LOAD_TIMESTAMP datetime YES


Sample Data:
============
"1028","768K","","","","N","","","","","","N","768000","Y","0.00","768000","768000","Y","N","2008.10.14 18:00:15"
"1029","832K","","","","N","","","","","","N","832000","Y","0.00","832000","832000","Y","N","2008.10.14 18:00:15"
"1030","896K","","","","N","","","","","","N","896000","Y","0.00","896000","896000","Y","N","2008.10.14 18:00:15"

SQL to perform data load:
=========================
USE MY_DATABASE;
SET SESSION SQL_MODE="ALLOW_INVALID_DATES";
TRUNCATE TABLE MY_TABLE;
LOAD DATA INFILE "K:/MySampleData.csv"
INTO TABLE MY_TABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

Options: ReplyQuote


Subject
Views
Written By
Posted
Excessive warning messages on import
9808
October 21, 2008 11:14AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.