RE: Re: Problem with load data and NULL

2005-11-08 Thread Barbara Deaton 
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 |
 | -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]

Re: Problem with load data and NULL

2005-11-08 Thread Harald Fuchs 
In article [EMAIL PROTECTED],
Barbara Deaton [EMAIL PROTECTED] writes:

 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?

This works fine for me, but fortunately I don't use Windoze.  Perhaps
you need to mess with LOAD DATE ... LINES TERMINATED BY '...'?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problem with load data and NULL

2005-11-05 Thread Harald Fuchs 
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 |
 | -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]

Problem with load data and NULL

2005-11-04 Thread Barbara Deaton 
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 |
| -00-00 |2 |
++--+


What can I say on the LOAD DATA command so the data is loaded as NULL and not 0?

I'm on windows and I've used the 4.1 and 5.0 clients to get this to work.

Thanks for your help.
Barbara

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]