List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 26 2002 6:03pm
Subject:Re: LOAD DATA INFILE sets blanks to 0 instead of NULL
View as plain text  
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);

Thread
LOAD DATA INFILE sets blanks to 0 instead of NULLPhilip Mak26 Feb
  • Re: LOAD DATA INFILE sets blanks to 0 instead of NULLPaul DuBois26 Feb
    • Re: LOAD DATA INFILE sets blanks to 0 instead of NULLPhilip Mak26 Feb
      • Re: LOAD DATA INFILE sets blanks to 0 instead of NULLPaul DuBois26 Feb