Hi-
I'm using the command 'mysqlimport -u usr -ppassh -h mysqlserver -P 3306 -v
db --local $workdir/$filename'to import a table into mysql from a file
$filename.

The data in $filename looks something like:

test test

where there is a blank space between the two 'test's to represent an empty
column. This column is of type datetime NULL DEFAULT NULL in the database.
The blank space between the two 'test's gets imported as (err) instead of
NULL.


Is there a way I can import empty column data as NULL? I'd like there to be
a NULL for every row for which this column is empty. I'm not sure how to do
this given the format of my $filename. As a check, I tried inserting a row
at the top of $filename like

test

The row was imported correctly, as in there was a NULL in the timestamp
column in the database, as well as a NULL in the next column in the
database. So, this may have something to do with the format of the
$filename, but I may not be able to control the format of that file.

Thanks.

qt4x11 wrote:
> Hi-
> I'm using the command 'mysqlimport -u usr -ppassh -h mysqlserver -P 3306 -v
> db --local $workdir/$filename'to import a table into mysql from a file
> $filename.
>
> The data in $filename looks something like:
>
> test test
>
> where there is a blank space between the two 'test's to represent an empty
> column. This column is of type datetime NULL DEFAULT NULL in the database.
> The blank space between the two 'test's gets imported as (err) instead of
> NULL.
>
>
> Is there a way I can import empty column data as NULL? I'd like there to be
> a NULL for every row for which this column is empty. I'm not sure how to do
> this given the format of my $filename. As a check, I tried inserting a row
> at the top of $filename like
>
> test
>
> The row was imported correctly, as in there was a NULL in the timestamp
> column in the database, as well as a NULL in the next column in the
> database. So, this may have something to do with the format of the
> $filename, but I may not be able to control the format of that file.
>
> Thanks.
>

You should alter your data like so:

test \N test

That is, separate the fields with a tab and place "\N" wherever you want
a NULL. As it is, the data is being misinterpreted, which is why the
second import you mentioned worked--there was nothing after the first
field to be erroneously put into the timestamp field.

If the data comes from somewhere else you will need to parse it out and
re-write it using proper formatting, i'm afraid.

brian