Load Data Gives Incorrect integer value: 'NULL'

Hi Experts,

I am trying to import a big csv file through the command prompt because it is too large for phpMyAdmin, but I keep getting Incorrect integer value: 'NULL'

Below I am including my command, the error and the table structure, and you will see that the indicated column does accept NULL.

mysql> LOAD DATA INFILE "D:\\reservations.csv" INTO TABLE reservations FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
ERROR 1366 (HY000): Incorrect integer value: 'NULL' for column 'niagara_trip_id' at row 1

mysql> describe reservations;
+---------------------+---------------+------+-----+---------+----------------+
| Field               | Type          | Null | Key | Default | Extra          |
+---------------------+---------------+------+-----+---------+----------------+
| id                  | int(11)       | NO   | PRI | NULL    | auto_increment |
| user_initials       | char(2)       | YES  | MUL | NULL    |                |
| user_id             | int(11)       | YES  | MUL | NULL    |                |
| product             | varchar(6)    | NO   | MUL | NULL    |                |
| trip_type           | char(1)       | YES  | MUL | NULL    |                |
| pickup_status       | char(1)       | NO   | MUL | O       |                |
| combo_assoc_id      | int(11)       | NO   | MUL | 0       |                |
| niagara_trip_id     | int(11)       | YES  | MUL | NULL    |                |
| niagara_return_id   | int(11)       | YES  |     | NULL    |                |
| archived            | tinyint(4)    | NO   | MUL | 0       |                |
| booking_date        | date          | NO   | MUL | NULL    |                |
| booking_time        | time          | NO   | MUL | NULL    |                |
| agent_id            | int(11)       | NO   | MUL | -1      |                |
| booking_location    | int(11)       | NO   | MUL | 0       |                |
| pickup_location     | int(11)       | NO   | MUL | 0       |                |
| pickup_date         | date          | NO   | MUL | NULL    |                |
| pickup_time         | time          | NO   | MUL | NULL    |                |
| driver_id           | int(11)       | NO   | MUL | 1       |                |
| vehicle_id          | int(11)       | NO   | MUL | 0       |                |
| group_name          | varchar(50)   | YES  | MUL | NULL    |                |
| room                | varchar(25)   | YES  |     | NULL    |                |
| reserv_comment      | varchar(4000) | YES  |     | NULL    |                |
| family_qty          | smallint(4)   | NO   |     | 0       |                |
| adult_qty           | smallint(4)   | NO   |     | 0       |                |
| senior_qty          | smallint(4)   | NO   |     | 0       |                |
| student_qty         | smallint(4)   | NO   |     | 0       |                |
| child_qty           | smallint(4)   | NO   |     | 0       |                |
| infant_qty          | smallint(4)   | NO   |     | 0       |                |
| total_pax           | smallint(5)   | NO   |     | 0       |                |
| family_rate         | decimal(6,2)  | NO   |     | 0.00    |                |
| adult_rate          | decimal(6,2)  | NO   |     | 0.00    |                |
| senior_rate         | decimal(6,2)  | NO   |     | 0.00    |                |
| student_rate        | decimal(6,2)  | NO   |     | 0.00    |                |
| child_rate          | decimal(6,2)  | NO   |     | 0.00    |                |
| infant_rate         | decimal(6,2)  | NO   |     | 0.00    |                |
| family_cost         | decimal(8,2)  | NO   |     | 0.00    |                |
| adult_cost          | decimal(8,2)  | NO   |     | 0.00    |                |
| senior_cost         | decimal(8,2)  | NO   |     | 0.00    |                |
| student_cost        | decimal(8,2)  | NO   |     | 0.00    |                |
| child_cost          | decimal(8,2)  | NO   |     | 0.00    |                |
| infant_cost         | decimal(8,2)  | NO   |     | 0.00    |                |
| total_cost          | decimal(9,2)  | NO   |     | 0.00    |                |
| trans_status        | varchar(10)   | NO   | MUL | Pending |                |
| deposit             | decimal(8,2)  | NO   |     | 0.00    |                |
| trans_due           | decimal(8,2)  | NO   |     | 0.00    |                |
| cash                | decimal(9,2)  | NO   |     | 0.00    |                |
| credit_card         | decimal(9,2)  | NO   |     | 0.00    |                |
| debit               | decimal(9,2)  | NO   |     | 0.00    |                |
| prepaid             | decimal(9,2)  | NO   |     | 0.00    |                |
| trans_collected     | decimal(9,2)  | NO   |     | 0.00    |                |
| trans_paym_loc_type | char(1)       | YES  |     | NULL    |                |
| trans_paym_loc      | int(11)       | NO   |     | 0       |                |
| trans_payee_type    | char(1)       | YES  |     | NULL    |                |
| trans_payee_id      | int(11)       | NO   |     | 0       |                |
| trans_paym_date     | date          | YES  |     | NULL    |                |
| trans_paym_time     | time          | YES  |     | NULL    |                |
| trans_comment       | varchar(4000) | YES  |     | NULL    |                |
| comm_status         | varchar(5)    | NO   | MUL | Owe     |                |
| family_comm         | decimal(6,2)  | NO   |     | 0.00    |                |
| adult_comm          | decimal(6,2)  | NO   |     | 0.00    |                |
| senior_comm         | decimal(6,2)  | NO   |     | 0.00    |                |
| student_comm        | decimal(6,2)  | NO   |     | 0.00    |                |
| child_comm          | decimal(6,2)  | NO   |     | 0.00    |                |
| infant_comm         | decimal(6,2)  | NO   |     | 0.00    |                |
| family_comm_total   | decimal(8,2)  | NO   |     | 0.00    |                |
| adult_comm_total    | decimal(8,2)  | NO   |     | 0.00    |                |
| senior_comm_total   | decimal(8,2)  | NO   |     | 0.00    |                |
| student_comm_total  | decimal(8,2)  | NO   |     | 0.00    |                |
| child_comm_total    | decimal(8,2)  | NO   |     | 0.00    |                |
| infant_comm_total   | decimal(8,2)  | NO   |     | 0.00    |                |
| comm_total          | decimal(8,2)  | NO   |     | 0.00    |                |
| comm_owe            | decimal(8,2)  | NO   |     | 0.00    |                |
| comm_paid           | decimal(8,2)  | NO   |     | 0.00    |                |
| comm_payer          | int(11)       | NO   |     | 0       |                |
| comm_paid_to        | varchar(50)   | YES  |     | NULL    |                |
| comm_paym_date      | date          | YES  |     | NULL    |                |
| comm_paym_time      | time          | YES  |     | NULL    |                |
| comm_rep_user       | int(11)       | NO   |     | 0       |                |
| comm_comments       | varchar(4000) | YES  |     | NULL    |                |
+---------------------+---------------+------+-----+---------+----------------+
79 rows in set (0.01 sec)

Open in new window


Any help will be greatly appreciated!
APD TorontoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sachiekCommented:
Try this one..

LOAD DATA LOCAL INFILE "Films.csv"
    INTO TABLE Films
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES
    -- Put the numeric fields in user variables (names prefixed by @)
  (@Intornumeric_coulmn)

set Intornumeric_coulmn = if(@Intornumeric_coulmn="", null, @Intornumeric_coulmn)
0
Dave BaldwinFixer of ProblemsCommented:
If there is an extra '\r\n' at the beginning of the file, maybe you need to ignore 2 lines.
0
APD TorontoAuthor Commented:
I dont understand regarding the  (@Intornumeric_coulmn)
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

APD TorontoAuthor Commented:
Dave, line 1 is column names
0
Dave BaldwinFixer of ProblemsCommented:
I understand that it should be, I was wondering if there was an un-intended '\r\n' before that.
0
sachiekCommented:
that is user variable
0
APD TorontoAuthor Commented:
Sachiek, but I know all variables are numeric or null
0
Dave BaldwinFixer of ProblemsCommented:
The reason I asked about an extra '\r\n' at the beginning of the file is that the error message is one that you typically get when you have the column names in the first line and forget to skip 1 line.
0
Julian HansenCommented:
Sounds like it is trying to put the value 'NULL' i.e. the string value - into an integer field.

Can you post a sample of the file you are trying to import?

If you can edit your file you can replace the NULL values with \N - no quotes - that should also fix the problem
0
APD TorontoAuthor Commented:
I tried ingnoring 2 lines, same issue.

The following is a sample of my file:

fldReservID,fldUserInit,fldUser,fldProductID,fldTripType,fldPickupStat,fldAssocResID,fldTripSubType,fldCSSNRetID,fldArchived,Booking_Date,Booking_Time,fldAgent,fldBookLoc,fldPickupLoc,Pickup_Date,Pickup_Time,fldDriver,fldVehicle,fldLName,fldRoomNo,fldReservComment,fldFamQty,fldAdultQty,fldSenQty,fldStudQty,fldChildQty,fldInfQty,total_pax,fldFamRate,fldAdultRate,fldSenRate,fldStudRate,fldChildRate,fldInfRate,fam_cost,ad_cost,sn_cost,st_cost,ch_cost,inf_cost,total_cost,trans_status,fldDeposit,trans_due,fldPaymCash,fldPaymCC,fldPaymDebit,prepaid,trans_collected,fldPaymLocType,fldPaymLoc,fldPayeeType,fldPayee,Trans_Paym_Date,Trans_Paym_Time,fldTransComment,comm_status,fldFamComm,fldAdultComm,fldSenComm,fldStudComm,fldChildComm,fldInfComm,fam_comm_total,ad_comm_total,sn_comm_total,st_comm_total,ch_comm_total,inf_comm_total,comm_total,comm_owe,fldCommPaid,fldPayer,fldPaidTo,Comm_Paym_Date,Comm_Paym_Time,fldReportUser,fldCommComment
3613,RG,29,CSST1,T,C,0,NULL,NULL,0,2011-01-02,9:18,25,2,2,2011-01-02,9:45,13,3,NULL,NULL,deposit paid,0,2,0,3,0,0,5,115,39.5,35,35,20,0,0,79,0,105,0,0,184,Settled,60,124,0,124,0,0,124,L,21,P,63,2011-01-02,9:45,,Paid,35,15,10,10,5,0,0,30,0,30,0,0,60,0,0,1,,2011-01-02,9:18,29,
3614,RG,29,CSST1,T,C,0,NULL,NULL,0,2011-01-02,9:19,25,2,2,2011-01-02,10:00,13,3,NULL,NULL,deposit paid,0,3,0,1,0,0,4,115,39.5,35,35,20,0,0,118.5,0,35,0,0,153.5,Settled,55,98.5,98.5,0,0,0,98.5,L,21,P,54,2011-01-02,10:00,,Paid,35,15,10,10,5,0,0,45,0,10,0,0,55,0,55,1,,2011-01-02,9:19,29,
3615,RG,29,CSST1,T,C,0,NULL,NULL,0,2011-01-02,9:29,236,15,15,2011-01-02,10:20,1,0,grlech,NULL,,0,4,0,0,0,0,4,115,34,35,35,20,0,0,136,0,0,0,0,136,Settled,0,136,136,0,0,0,136,V,3,D,13,2011-01-02,10:20,,Paid,30,12.5,12.5,12.5,5,0,0,50,0,0,0,0,50,50,50,3,mary,2011-01-14,12:00,1,
3616,RG,29,CSST1,T,C,0,NULL,NULL,0,2011-01-02,10:31,53,23,23,2011-01-02,11:00,1,0,fantunina,NULL,,0,2,0,0,0,0,2,115,39.5,35,35,20,0,0,79,0,0,0,0,79,Settled,0,79,79,0,0,0,79,V,3,D,13,2011-01-02,11:00,,Paid,30,12.5,12.5,12.5,5,0,0,25,0,0,0,0,25,25,25,3,balla,2011-01-14,12:00,1,
3617,CN,1,CSST1,T,X,0,NULL,NULL,1,2011-01-03,9:29,198,58,58,2011-01-03,10:00,1,0,"Dr. Ani Keeff, Lynch Porsella",unknown,called in by Vanessa based on a message she received from the night before- the guests had specified 10:00. Unsure at time of booking if they were all adults or seniors etc. ** followup- they had in fact paid GL over the phone and it was a big confusing mess but in the end Chris called GL and told them not to bill us for these ones (Cornel had issued them tickets from us) and the comnmission is being taken away from Vanessa.,0,0,5,0,0,0,5,115,39.5,35,35,20,0,0,0,175,0,0,0,175,Settled,0,175,0,0,0,175,175,L,137,P,73,2011-01-03,10:00,,Paid,30,12.5,0,12.5,5,0,0,0,0,0,0,0,0,0,0,3,Vanessa KINGED,2011-08-30,13:00,2,
3618,CN,1,CSST1,T,C,0,NULL,NULL,0,2011-01-03,10:28,10,3,3,2011-01-03,11:45,1,0,CONNELLAN,1404,not sure of the exact number- will charge them all though,0,2,0,0,2,0,4,115,39.5,35,35,20,0,0,79,0,0,40,0,119,Settled,0,119,0,0,0,119,119,L,3,D,26,2011-01-03,11:45,,Paid,12.5,5,5,5,2.5,0,0,10,0,0,5,0,15,15,15,4,david,2011-01-03,10:28,1,
3619,CN,1,CSST1,T,C,0,NULL,NULL,0,2011-01-03,10:51,10,3,3,2011-01-03,11:45,1,0,satriale,2218,,0,2,0,0,0,0,2,115,39.5,35,35,20,0,0,79,0,0,0,0,79,Settled,0,79,0,0,0,79,79,L,3,D,26,2011-01-03,11:45,,Paid,12.5,5,5,5,2.5,0,0,10,0,0,0,0,10,10,10,2,david,2011-01-03,10:51,1,
3620,CN,1,CSST1,T,C,0,NULL,NULL,0,2011-01-04,10:34,27,2,2,2011-01-04,11:45,1,0,carlos,1129,taken dep- 50- arturo called this in on behalf of Virgilio and Asogan who had already been paid a deposit,0,2,0,2,0,0,4,115,39.5,35,35,20,0,0,79,0,70,0,0,149,Settled,50,99,0,99,0,0,99,L,147,D,4,2011-01-04,11:45,,Paid,35,15,10,10,5,0,0,30,0,20,0,0,50,0,50,1,asogan,2011-01-04,10:34,1,
3621,CN,1,CSST1,T,X,0,NULL,NULL,1,2011-01-04,13:09,62,20,20,2011-01-04,13:09,1,0,trial booking,trial booking,,0,0,0,0,0,0,0,115,39.5,35,35,20,0,0,0,0,0,0,0,0,Settled,0,0,0,0,0,0,0,V,3,D,4,2011-01-04,13:09,,Paid,30,12.5,12.5,12.5,5,0,0,0,0,0,0,0,0,0,0,3,test test,2011-01-05,12:00,1,

Open in new window

0
APD TorontoAuthor Commented:
What is interesting now is, I tried importing the above using phpMyAdmin, since it is small, no errors, but in command prompt the same error.
0
Julian HansenCommented:
Refer the LOAD DATA docs here https://dev.mysql.com/doc/refman/5.6/en/load-data.html
Handling of NULL values varies according to the FIELDS and LINES options in use:

    For the default FIELDS and LINES values, NULL is written as a field value of \N for output, and a field value of \N is read as NULL for input (assuming that the ESCAPED BY character is “\”).

    If FIELDS ENCLOSED BY is not empty, a field containing the literal word NULL as its value is read as a NULL value. This differs from the word NULL enclosed within FIELDS ENCLOSED BY characters, which is read as the string 'NULL'.

    If FIELDS ESCAPED BY is empty, NULL is written as the word NULL.

    With fixed-row format (which is used when FIELDS TERMINATED BY and FIELDS ENCLOSED BY are both empty), NULL is written as an empty string. This causes both NULL values and empty strings in the table to be indistinguishable when written to the file because both are written as empty strings. If you need to be able to tell the two apart when reading the file back in, you should not use fixed-row format.
You are not using FIELDS ENCLOSED BY so it is not seeing NULL (without quotes) as NULL - but as a string value.
For your file you would need to use \N as the NULL value or enclose your fields in quotes and add a FIELDS ENCLOSED BY
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.