From: Christian Mack
Date: March 12 1999 2:22pm
Subject: Re: Importing NULL values
List-Archive: http://lists.mysql.com/mysql/96
Message-Id: <36E92339.5F19F321@compal.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

garyl.cwa4.epa.gov@stripped wrote:
> 
> >Description:
> 
>         I am trying to do a LOAD DATA INFILE where the data file has NULL
> values.  For example, using the table:
> 
>      +--------+-------------+------+-----+---------+-------+
>      | Field  | Type        | Null | Key | Default | Extra |
>      +--------+-------------+------+-----+---------+-------+
>      | fname  | varchar(10) | YES  |     | NULL    |       |
>      | lname  | varchar(10) | YES  |     | NULL    |       |
>      | age    | int(4)      | YES  |     | NULL    |       |
>      | height | float(5,2)  | YES  |     | NULL    |       |
>      +--------+-------------+------+-----+---------+-------+
>      4 rows in set (0.16 sec)
> 
> and data file:
> 
>      "sally","rogers",,
>      "joe","blo",32,5.5
>      "test","",,
> 
> And submit the floowing query:
> 
>      LOAD DATA INFILE '/h/garyl/test.dat'
>      INTO TABLE test
>      FIELDS TERMINATED BY ','
>      OPTIONALLY ENCLOSED BY '"';
> 
> I get:
> 
>      mysql> select * from test;
>      +-------+--------+------+--------+
>      | fname | lname  | age  | height |
>      +-------+--------+------+--------+
>      | sally | rogers |    0 |   0.00 |
>      | joe   | blo    |   32 |   5.50 |
>      | test  |        |    0 |   0.00 |
>      +-------+--------+------+--------+
>      3 rows in set (0.18 sec)
> 
> Instead, I should have gotten:
> 
>      mysql> select * from test;
>      +-------+--------+------+--------+
>      | fname | lname  | age  | height |
>      +-------+--------+------+--------+
>      | sally | rogers | NULL |   NULL |
>      | joe   | blo    |   32 |   5.50 |
>      | test  | NULL   | NULL |   NULL |
>      +-------+--------+------+--------+
>      3 rows in set (0.02 sec)
> 
> Anyone know how to import data files with NULL values that show up as
> NULL values?
> 
> -Gary

Hi Gary

Sure I know.

Sorry couldn't withstand this one :)

You have to use NULL or \N (which must be uppercase) instead of leaving fields blank.

Tschau
CHristian