Error: Incorrect integer value: with Inserting tables (.csv files) into MariaDB/MySQL database

Question

I am trying to import the hotel table into the database. However, it seems I get an error that I cannot fix it spending hours! Here's the error I am getting:

Incorrect integer value: 'wolfinns1' for column 'hotelId' at row 1

The first columns in auto_increment, but it seems it attempts to read from the cvs file although there is no entries there! Here are the records for the hotels table and sql commands. By the way, I have converted all the files to Unix format.

==== hotesls.csv =============
wolfinns1,midas1st,Fayetteville,NC,9101231234,14
wolfinns2,midas2st,Raleigh,NC,9191231234,15
wolfinns3,midas3st,Los Angeles,CA,3101231234,16
wolfinns4,midas4st,New York,NY,2121231234,17
==========================

Here is the schema:

+-----------+-----------------+------+-----+---------+------------
| Field     | Type            | Null | Key | Default | Extra   
+-----------+-----------------+------+-----+---------+------------
| hotelId   | int(9) unsigned | NO   | PRI | NULL    | auto_increment 
| name      | varchar(50)     | NO   |     | NULL    |               
| address   | varchar(75)     | NO   |     | NULL    |                
| city      | varchar(50)     | NO   |     | NULL    |                
| state     | char(2)         | NO   |     | NULL    |                
| phone     | varchar(20)     | NO   | UNI | NULL    |                
| managerId | int(9) unsigned | NO   | MUL | NULL    |        
+-----------+-----------------+------+-----+---------+------------

Now issuing the sql command:

MariaDB [xzheng6]> LOAD DATA LOCAL INFILE 'hotels.csv' INTO TABLE hotels FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' STARTING BY '';
Query OK, 0 rows affected, 16 warnings (0.00 sec) 
Records: 4 Deleted: 0 Skipped: 4 Warnings: 16

Here are the warnings!

MariaDB [xzheng6]> show warnings\g
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'wolfinns1' for column 'hotelId' at row 1 |
| Warning | 1265 | Data truncated for column 'state' at row 1 |
| Warning | 1261 | Row 1 doesn't contain data for all columns

Thank you very much in advance. CS

Paul Campbell · Accepted Answer · 2018-03-06 20:55:13Z

1

From the mysql documentation

By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col_name_or_user_var [, col_name_or_user_var] ...);

So, provide a column list that doesn't include hotelID and you should be fine.

answered Mar 6, 2018 at 20:55

Paul Campbell

1,9862 gold badges14 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

chikitin Over a year ago

Thank you for the response. I actually did it that way, however, It gives me another warning and does not insert the row before the last row ( Ellis): Here are the three last rows:

Moran,MNGR,MNGD,Moran St.,Raleigh,NC,9191230000,1985-12-13 Ellis,NNGR,MNGD,Ellis St.,Los Angeles,CA,9101231111,1986-11-12 Abhijeet,MNGR,MNGD,Abhijeet St.,New York City,NY,9191232222,1987-12-12

. Here is my sql command: `LOAD DATA LOCAL INFILE 'staff.csv' INTO TABLE staff FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' STARTING BY '' (name,titleCode,deptCode,address,city,state,phone,DOB);'

chikitin Over a year ago

Also the Staff ID, the primary key, with auto_increment, does not start from 1 and starts from 249! Thanks again.

chikitin Over a year ago

There error I am getting is this: | Warning | 1452 | Cannot add or update a child row: a foreign key constraint fails (xzheng6.staff, CONSTRAINT staff_ibfk_1` FOREIGN KEY (titleCode) REFERENCES job_titles (titleCode)) |`

Paul Campbell Over a year ago

OK, so this is a different file upload and these are two new issues. The numbering is, I suspect, a result of you making multiple attempts at loading the data and deleting records after each attempt - that will not reset the auto_increment value between attempts. You need to use either TRUNCATE TABLE staff; or, if you don't have the permission to do that you could clear the table down with DELETE FROM staff; followed by ALTER TABLE staff AUTO_INCREMENT = 1; to achieve the same thing.

Paul Campbell Over a year ago

That said, this is an auto increment, a so called surrogate key. The ID is there to ensure you have a unique Primary Key the number itself doesn't actually represent anything meaningful, so whether that number is 1 ... 249 or 9875312 doesn't make any difference as far as mysql is concerned (but having shorter numbers might mean less typing for you). Next issue will go in a separate comment.

|

Collectives™ on Stack Overflow

Error: Incorrect integer value: with Inserting tables (.csv files) into MariaDB/MySQL database

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related