Error in saving csv to MySQL table due to NULL values

Question

I have the data saved in my csv excel. The data have some null value in row and column. I want to save this data into my database in MySQL. But null value is causing problem in saving the data to MySQL. This is the query for creating the table -

create table student (
  Std_ID int, 
  Roll_NO int, 
  First_Name varchar(10) NOT NULL, 
  Last_Name varchar(10), 
  Class int, 
  constraint test_student primary key (Std_ID)
);

...and it ran successfully. Now I want to save my data from csv to this table using the query -

load data infile 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\new.csv' into table student fields terminated by ',' lines terminated by '\n' ignore 1 lines;

...and is giving me the error msg -

ERROR 1366 (HY000): Incorrect integer value: '' for column 'XXX' at row X.

For the reference you can use this data. The same can be find below.

Is this a manual upload? Or do you need to script this. One thing I might do is create all varchar type fields in the database, import the CSV, then change the necessary fields to int in the database. — bloodyKnuckles
– bloodyKnuckles, Commented May 30, 2022 at 17:23
The error tells that the problematic value is not NULL but empty string. You must use input preprocessing for to solve. PS. What value do you want to insert instead this incorrect one? zero? NULL? etc. — Akina
– Akina, Commented May 30, 2022 at 17:37
@bloodyKnuckles This is no script defined for this and regarding changing all columns to varchar, it's causing some issue when I extract the data in python. — Vikesh Kr.
– Vikesh Kr., Commented Jun 1, 2022 at 1:38
@Akina The data is NULL it's not correct or incorrect. Since the data is empty string, what pre-processing can be done?? — Vikesh Kr.
– Vikesh Kr., Commented Jun 1, 2022 at 1:40

Akina · Accepted Answer · 2022-06-02 04:27:06Z

1

LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\new.csv' 
INTO TABLE student 
FIELDS TERMINATED BY ',' 
LINES TERMINATED by '\n' 
IGNORE 1 LINES
-- specify columns, use variables for the columns where incorrect value may occur
(Std_ID, @Roll_NO, First_Name, Last_Name, @Class)
-- use preprocessing, replace empty string with NULL but save any other value
SET Roll_NO = NULLIF(@Roll_NO, ''),
    Class = NULLIF(@Class, '');

If some column in CSV is empty string then NULL value will be inserted into according column of the table.

Std_ID is not preprocessed because it is defined as PRIMARY KEY, and it cannot be NULL.

UPDATE

OP provides source file sample. Viewing it in HEX mode shows that the file is Windows-style text file, and hence the lines terminator is '\r\n'. After according edition the file is imported successfuly.

edited Jun 2, 2022 at 4:27

answered May 30, 2022 at 17:43

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

6 Comments

Vikesh Kr. Over a year ago

the query is still causing a problem. There is no error msg but the query isn't getting executed. After entering the query, I'm getting the msg '' for column 'Class' at row 4 integer value: '' and the query doesn't executes.

Akina Over a year ago

Edit your question and add a sample of your CSV file content (3-5 rows) formatted as code. I'm getting the msg You must provide complete and unchanged error message, not a small part of it.

Vikesh Kr. Over a year ago

Data has been added in the question, it was there in the link in question and the statement is not a part of the error, it's complete. You can see the below statements. That's what I'm getting mysql> load data infile 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\SQL_Try.csv' into table student fields terminated by ',' lines terminated by '\n' ignore 1 lines (Std_Id, @Roll_No, First_Name, Second_Name, @Class) set Roll_No=NULLIF(@Roll_No,''), Class=NULLIF(@Class,''); ' for column 'Class' at row 4 integer value: ' mysql>

Akina Over a year ago

@Vikeshk Well, I look at your source data. You can easily look at the problem source. When the query fails the variable @Class stores the most last loaded value which causes an error. I.e. immediately after errorneous attempt execute SELECT HEX(@Class) - and you will see that the variable contains 0D value. If you open the file in HEX viewer then you can see that each line is terminated with 0D 0A bytes (the text is Window-style text) which has lines terminator \r\n whereas you tell MySQL that LINES TERMINATED by '\n'. So this is your error, not query error.

Vikesh Kr. Over a year ago

I didn't understood completely, I'll try to understand this but why it's causing problem only for the last column not for the column Roll_No?

|

Collectives™ on Stack Overflow

Error in saving csv to MySQL table due to NULL values

1 Answer 1

UPDATE

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related