Auto_Increment Issue

Hello, my understanding was that LOAD INFILE worked similar to INSERT,
AUTO_INCREMENT should take care of my id column. Fields here are
delimited by @. If I do not include the leading @ then the data loads
offset with month trying to fill "id". But WITH the leading @ the data
imports correctly as far as I can tell but I receive this error after
LOAD INFILE ...

1366 Incorrect integer value: " for column 'id' at row 1

and again for each row thereafter.

my data looks like:

@"2007-01-01"@3@"BBHORU"@"LAM"@"CF PLAID 8 X 11 TALL"@"RED"@"STPLD"@
1@8.75@6.25

and table structure:

CREATE TABLE `esas`.`lineitems` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`month` DATE NOT NULL DEFAULT 0,
`item` VARCHAR(5) NOT NULL DEFAULT '',
`dsc` VARCHAR(6) NOT NULL DEFAULT '',
`vendor` VARCHAR(3) NOT NULL DEFAULT '',
`description` VARCHAR(40) NOT NULL DEFAULT '',
`attr` VARCHAR(15) DEFAULT '',
`size` VARCHAR(15) DEFAULT '',
`qty` INTEGER NOT NULL DEFAULT 0,
`price` FLOAT NOT NULL DEFAULT 0,
`cost` FLOAT NOT NULL DEFAULT 0,
PRIMARY KEY (id)
)
ENGINE = InnoDB;

The actual LOAD syntax I used:

LOAD DATA LOCAL INFILE '~/app/csv_files/rpli0701b.csv'
INTO TABLE lineitems
FIELDS TERMINATED BY '@'
ENCLOSED BY '"'
IGNORE 1 LINES;

The actual data loaded into the table looks fine but I am afraid
to go too much further with this error as I have about fifty files to
LOAD. Should I be worried?

--Zef

On Thu, 01 Mar 2007 10:21:07 -0800, Zefarin <Zefarin@notlikely.com>
wrote:

Answering my own question. Place the keyword NULL before the leading
delimiter, @.

--Zef

>Hello, my understanding was that LOAD INFILE worked similar to INSERT,
>AUTO_INCREMENT should take care of my id column. Fields here are
>delimited by @. If I do not include the leading @ then the data loads
>offset with month trying to fill "id". But WITH the leading @ the data
>imports correctly as far as I can tell but I receive this error after
>LOAD INFILE ...
>
>1366 Incorrect integer value: " for column 'id' at row 1
>
>and again for each row thereafter.
>
>my data looks like:
>
>@"2007-01-01"@3@"BBHORU"@"LAM"@"CF PLAID 8 X 11 TALL"@"RED"@"STPLD"@
>1@8.75@6.25
>
>and table structure:
>
>CREATE TABLE `esas`.`lineitems` (
> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
> `month` DATE NOT NULL DEFAULT 0,
> `item` VARCHAR(5) NOT NULL DEFAULT '',
> `dsc` VARCHAR(6) NOT NULL DEFAULT '',
> `vendor` VARCHAR(3) NOT NULL DEFAULT '',
> `description` VARCHAR(40) NOT NULL DEFAULT '',
> `attr` VARCHAR(15) DEFAULT '',
> `size` VARCHAR(15) DEFAULT '',
> `qty` INTEGER NOT NULL DEFAULT 0,
> `price` FLOAT NOT NULL DEFAULT 0,
> `cost` FLOAT NOT NULL DEFAULT 0,
> PRIMARY KEY (id)
>)
>ENGINE = InnoDB;
>
>The actual LOAD syntax I used:
>
>LOAD DATA LOCAL INFILE '~/app/csv_files/rpli0701b.csv'
>INTO TABLE lineitems
>FIELDS TERMINATED BY '@'
>ENCLOSED BY '"'
>IGNORE 1 LINES;
>
>The actual data loaded into the table looks fine but I am afraid
>to go too much further with this error as I have about fifty files to
>LOAD. Should I be worried?
>
>--Zef