Hi,

I have a table with a DATE field that can be NULL and the default is NULL.
The table definition is shown below with all the fields not shown except
the one in question.

CREATE TABLE MyTable (
....
SepDate DATE NULL DEFAULT NULL,
....
) ENGINE = InnoDB;

I populate MyTable using a csv file that comes directly from
an Excel spreadsheet. The SQL command is:

LOAD DATA LOCAL INFILE '2011-03-28.csv'
INTO TABLE Brios.Brio2011_03_28
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
.... more omitted

The terminator is a pipe symbol because I'm sure it does not appear as
data in the Excel file. It all works well except for the following minor
irritation.

Most, not all, of the DATE columns in the csv file are blank,
they appear as two consecutive pipe symbols with nothing in between,
i.e., ||.
After the LOAD these blank DATE values (||) appear in MyTable as
'0000-00-00'. I would like them to appear as NULL.

I realize I can do a query that would replace all '0000-00-00' with NULL
but I was hoping there would be another way.

Thank you,
Joe

El 30/03/2011 19:39, Joe Hesse escribió/wrote:
You can use a function to manipulate the input data if you store it in a
temporary variable. For instance, this is what I use to replace commas
with periods in numbers:

LOAD DATA LOCAL INFILE 'C:/path/to/mytable.txt' IGNORE
INTO TABLE mytable
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'
(int_col, @float_col)
SET float_col = replace(@float_col, ',', '.');

MySQL casts the value into the column type when it reads it from the
file, *before* applying the transformations described in the SET clause.
If you instruct it to read it into a variable the value is handled as
string from the beginning.

In your case, you can do something like:

SET SepDate = CASE @SepDate WHEN '' THEN NULL ELSE @SepDate END

It's also interesting to disable the ALLOW_INVALID_DATES SQL mode:

http://dev.mysql.com/doc/refman/5.1/..._invalid_dates



--
-- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

El 31/03/2011 9:34, "�lvaro G. Vicario" escribió/wrote:
Or maybe:

SET SepDate = CASE @SepDate WHEN '0000-00-00' THEN NULL ELSE @SepDate END

I haven't really tested it


--
-- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

In article <in1bf4$tbg$2 (AT) dont-email (DOT) me>,
à lvaro G. Vicario <alvaro.NOSPAMTHANX (AT) demogracia (DOT) com.invalid> wrote:
Or more concisely:

SET SepDate = NULLIF(@SepDate,'')

SET SepDate = NULLIF(@SepDate,'0000-00-00')

Neither have I! :-)

Tony
--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org

On Mar 31, 8:45*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:
alternative results. You should use IF() (except where as Tony pointed
out NULLIF() will do.

El 31/03/2011 10:48, Tony Mountifield escribió/wrote:
Certainly. I tend to abuse CASE because it's the only construct that
doesn't force me to think what DBMS I'm currently using

That, and the fact that I can never remember the differences between
IF(), IFNULL and NULLIF()...

http://dev.mysql.com/doc/refman/5.1/...functions.html



--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--