“Load data” loads everything but 1 column (genre). What is strange is that I had no problem loading that column in books table. So then I decided to create books2 and add an auto-increment id field to the structure. Tried to load the data into books2. It loaded everything but the second column (genre).
I get
*************************** 1. row ***************************
Level: Warning
Code: 1366
Message: Incorrect integer value: ‘Picturebooks-Fiction’ for column ‘id’ at row 1
*************************** 2. row ***************************
Level: Warning
Code: 1265
Message: Data truncated for column ‘isbn’ at row 1
*************************** 3. row ***************************
create table t_books2 (
id int not null auto_increment primary key,
genre char(30),
author char(50) not null,
title char(100) not null,
isbn char(25),
url char(100)
) default character set utf8;
I am trying to load from an EXCEL file which has its columns in the same order except no ID. But mySQL is supposed to automatically add that ID.
load data local infile 'c:/test/summer2011e.txt'
into table t_books2
fields terminated by '\ '
enclosed by ''
optionally enclosed by '"'
escaped by '\\\\'
lines terminated by '\\r\
';
Message: Incorrect integer value: ‘Picturebooks-Fiction’ for column ‘id’ at row 1
This means that in the data file you have for the id of a book the value '‘Picturebooks-Fiction’, which is not an integer, but in the database schema you did say it should be an integer. Since it isn’t MySQL won’t insert it. You can either change the value in the data file to an integer, or change the database schema to use varchar for the ID instead. Generally I’d go with changing the data file, but since I don’t know the specifics of what you’re doing that may or may not be appropriate here.
Message: Data truncated for column ‘isbn’ at row 1
This means that the value for the ISBN is too long. In the database schema you have isbn set to CHAR(25) so it can be a maximum of 25 characters (including spaces!). For example if I use a CHAR(6) to store ‘ScallioXTX’ it would just store ‘Scallio’ and emit the warning you got.
The solution here is to either check if there are characters in your data file that can be removed (like spaces or dashes you don’t really need), or to change CHAR(25) to something more suitable.
HTH, and if you have any follow up questions, just ask
Thanks. That helped me to understand mySQL and to clarify my mind. I want to load a data file which has no ID field and have mySQL add that ID.
I went back to the manual and searched for more info on LOAD DATA and columns. I chose the way that was simplest for me: change the table structure so that ID is last.
create table t_books3 (
genre char(30),
author char(50) not null,
title char(100) not null,
isbn char(25),
url char(100),
id int not null auto_increment primary key
) default character set utf8;
This works. I still get one kind of warning: “Message: Row 1 doesn’t contain data for all columns” but that is only true and does not affect the table.
Thx for the suggestion. I am new to mySQL and was not familiar with VARCHAR. After reading up, I understand that VARCHAR takes up only as much room as the stored text requires which makes for shorter rows. Thus more rows can fit onto the block of disk space. This makes input and output faster for the same # of rows.
the other thing you can do when you are only loading specific columns is to tell that info as well:
load data local infile
'c:/test/summer2011e.txt'
into table t_books2
fields terminated by '\ '
enclosed by ''
optionally enclosed by '"'
escaped by '\\\\'
lines terminated by '\\r\
'
(genre, author, title, isbn, url)
then it knows what order the excel file columns are and what columns to enter and would add the id column automatically. it would then not need the redesign of your table (which in this case is trivial but in other cases it may not be.
