List:General Discussion« Previous MessageNext Message »
From:Chris Date:March 12 1999 4:47pm
Subject:Re: Importing NULL values
View as plain text  
I belive that '\N' in the data file will be interpreted as a NULL value...

--Chris

On Thu, 11 Mar 1999 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
> 
> >Submitter-Id:	<submitter ID>
> >Originator:	Gary G. Lear
> >Organization:
>   -----------------------------------
>   Gary Lear
>   garyl@stripped
>   OAR/Acid Rain Division
>   401 M Street SW (6204J)
>   Washington DC 20460
>   202 564-9179
> >
> >MySQL support: none
> >Synopsis:	Null data vales not loading as NULL
> >Severity:	non-critical
> >Priority:	medium
> >Category:	mysql
> >Class:		sw-bug
> >Release:	mysql-3.22.15-gamma (TCX binary)
> >Server: /usr/local/bin/mysqladmin  Ver 7.8 Distrib 3.22.15-gamma, for
> sun-solaris2.6 on sparc
> TCX Datakonsult AB, by Monty
> 
> Server version		3.22.15-gamma-log
> Protocol version	10
> Connection		Localhost via UNIX socket
> UNIX socket		/tmp/mysql.sock
> Uptime:			2 days 19 hours 1 min 12 sec
> 
> Threads: 4  Questions: 72231  Slow queries: 96  Opens: 167  Flush tables: 1  Open
> tables: 28
> >Environment:
> 	
> System: SunOS pomo 5.6 Generic_105181-06 sun4m sparc SUNW,SPARCstation-20
> Architecture: sun4
> 
> Some paths:  /usr/local/bin/perl /usr/local/bin/make /usr/local/bin/gcc /usr/ucb/cc
> GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.6/2.8.0/specs
> gcc version 2.8.0
> Compilation info: CC='gcc'  CFLAGS='-O3 -fomit-frame-pointer'  CXX='gcc' 
> CXXFLAGS='-O3 -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' 
> LDFLAGS=''
> Configure command: ./configure  --prefix=/usr/local/mysql '--with-comment=TCX binary'
> --with-low-memory --disable-shared
> Perl: This is perl, version 5.004_04 built for sun4-solaris
> 
> ---------------------------------------------------------------------
> To request this thread, e-mail mysql-thread73@stripped
> 
> To unsubscribe, e-mail the address in the List-Unsubscribe header.
> For additional commands, e-mail: mysql-help@stripped
> 

Thread
Importing NULL valuesgaryl.cwa4.epa.gov12 Mar
  • Re: Importing NULL valuesChris12 Mar
  • Re: Importing NULL valuesThimble Smith12 Mar
  • Re: Importing NULL valuesMicheal Mc Evoy12 Mar
  • Re: Importing NULL valuesChristian Mack12 Mar
  • Importing NULL valuesMichael Widenius15 Mar