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
