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/, $_, 10000); # 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
