In article <[EMAIL PROTECTED]>,
"Barbara Deaton" <[EMAIL PROTECTED]> writes:
> All,
> I need help with using load data to load a text file that is created by
> another application.
> I have a text file that contains 2 columns, date and smallint and 2 rows:
> d,e
> 2005-12-31,
> ,2
> In the above example the first row, column e should be NULL and the second
> row column d should be NULL. The application that outputs this text files
> does not output a \N for null it leave the data empty.
> So when I load the data into MySQL, even though I have created the table as:
> CREATE TABLE `a` (`d` date default NULL,`e` smallint(6) default NULL)
> ENGINE=InnoDB DEFAULT CHARSET=utf8
> The Load Data commands inserts a 0 for my values so the table looks like:
> +------------+------+
> | d | e |
> +------------+------+
> | 2005-12-31 | 0 |
> | 0000-00-00 | 2 |
> +------------+------+
> What can I say on the LOAD DATA command so the data is loaded as NULL and not
> 0?
You could use a temporary table for that:
CREATE TEMPORARY TABLE tmp (
d TEXT NOT NULL,
e TEXT NOT NULL
);
LOAD DATA LOCAL INFILE 'yourfile'
INTO TABLE tmp
FIELDS TERMINATED BY ',';
INSERT INTO a (d, e)
SELECT CASE d WHEN '' THEN NULL ELSE d END,
CASE e WHEN '' THEN NULL ELSE e END
FROM tmp;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
