LOAD DATA INFILE sets blanks to 0 instead of NULL
I made an Excel spreadsheet containing grades of students in my class, like this: (name) (email) (assignment 1 grade)(assignment 2 grade) For when a student did not submit an assignment, I just left the field blank. I'm trying to import the spreadsheet into a MySQL table of the following format: name VARCHAR(30) email VARCHAR(30) a1 TINYINT a2 TINYINT When I typed the following: load data local infile 'grades.txt' into table grades fields terminated by '\t'; it set the integer columns to 0 if they were blank in the text file. How can I make it set those to NULL instead? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOAD DATA INFILE sets blanks to 0 instead of NULL
At 12:43 -0500 2/26/02, Philip Mak wrote:
I made an Excel spreadsheet containing grades of students in my class,
like this:
(name) (email) (assignment 1 grade)(assignment 2 grade)
For when a student did not submit an assignment, I just left the field
blank.
I'm trying to import the spreadsheet into a MySQL table of the
following format:
name VARCHAR(30)
email VARCHAR(30)
a1 TINYINT
a2 TINYINT
When I typed the following:
load data local infile 'grades.txt' into table grades
fields terminated by '\t';
it set the integer columns to 0 if they were blank in the text file.
How can I make it set those to NULL instead?
You'll have to preprocess the file to convert empty fields to \N.
#! /usr/bin/perl -w
# empty_to_null.pl - Convert empty input fields to \N.
# Assumes tab-delimited, linefeed-terminated input lines.
use strict;
while ()
{
chomp;
my @val = split (/\t/, $_, 1); # split, preserving all fields
# map empty fields to \N, write as tab-delimited output line
print join (\t, map { /^$/ ? \\N : $_ } @val) . \n;
}
exit (0);
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOAD DATA INFILE sets blanks to 0 instead of NULL
On Tue, Feb 26, 2002 at 12:03:25PM -0600, Paul DuBois wrote: it set the integer columns to 0 if they were blank in the text file. How can I make it set those to NULL instead? You'll have to preprocess the file to convert empty fields to \N. Damn, that's what I thought. I guess I'll have to do that for now; thanks for the perl code. I think that it would have made sense for MySQL to treat empty fields as NULL when importing into a numeric column. (Right now, the empty field causes a warning anyway since it converts it to 0.) Is there a place where I can put this in as a feature request (or is there a good reason that it shouldn't do this)? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOAD DATA INFILE sets blanks to 0 instead of NULL
At 13:08 -0500 2/26/02, Philip Mak wrote: On Tue, Feb 26, 2002 at 12:03:25PM -0600, Paul DuBois wrote: it set the integer columns to 0 if they were blank in the text file. How can I make it set those to NULL instead? You'll have to preprocess the file to convert empty fields to \N. Damn, that's what I thought. I guess I'll have to do that for now; thanks for the perl code. I think that it would have made sense for MySQL to treat empty fields as NULL when importing into a numeric column. (Right now, the empty field causes a warning anyway since it converts it to 0.) An empty field is not NULL. It's an empty string. MySQL performs an implicit string-to-number conversion when loading the field, which results in a value of zero, and a warning. Is there a place where I can put this in as a feature request (or is there a good reason that it shouldn't do this)? The good reason is that LOAD DATA has a convention for representing NULL, and your file violates it. :-) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
