Thank you for the idea. It fixed my date problem, but my numeric column is
still 0 and NOT "NULL." Here's what I did:
create table a (
d date default null,
e smallint default null );
CREATE TEMPORARY TABLE tmp (
d TEXT not NULL,
e TEXT not NULL );
LOAD DATA LOCAL INFILE "c:\\temp\\mysql.txt"
INTO TABLE tmp
FIELDS TERMINATED BY ','
IGNORE 1 LINES;
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> select * from a;
+------------+------+
| d | e |
+------------+------+
| 2005-12-31 | 0 |
| NULL | 2 |
+------------+------+
Is there anything else I can do?
Thank you for your time and any information.
-Barb.
-----Original Message-----
From: news [mailto:[EMAIL PROTECTED] On Behalf Of Harald Fuchs
Sent: Saturday, November 05, 2005 10:03 AM
To: mysql@lists.mysql.com
Subject: Re: Problem with load data and NULL
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
