CODEWITHSUNDEEP
mysqlsqldatabasecsv
Marc
Marc

Reputation: 650

NULL values are not inserted into sql table

I have a CSV file with three columns of "movieId", "imdbId", "tmdbId". The column "tmdbId" contains multiple empty rows.(movieId is a froeign key referring to a primary key in another table)

When I read this data frame into R, the empty rows are treated as NA values. If I import this CSV file into mysql DB using the following command, the rows with NA values don’t get inserted in the table, even though I allow NULL values. I should also mention that, I do not get any errors.

Beside the following command, I also tried importing the dataset using MySQL workbench, but it did not work.

any suggestion?

LOAD DATA LOCAL INFILE 'links.csv' INTO TABLE links
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(movieId, imdbId, tmdbId);

I know NULL and NA values are not the same, but I do not understand why R treats empty rows as NA. I tried to replace NA with NULL, but R does not support this operation.

The TABLE

CREATE TABLE links ( 
    movieId int NOT NULL, 
    imdbId int DEFAULT NULL, 
    tmdbId int DEFAULT NULL, 
    KEY movieId (movieId), 
    CONSTRAINT links_ibfk_1 FOREIGN KEY (movieId) REFERENCES movieId_title (movieId) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The CSV file looks like this: enter image description here

Here is an example of an empty row for the third column: enter image description here

Upvotes: 2

Views: 1225

Answers (1)

M M
M M

Reputation: 448

As @Alec suggested, you can do set foreign_key_checks = 0. Then, you can replace zeros with NULL using the following command:

UPDATE table_name
SET col_name= NULL 
WHERE  col_name  = 0;

Upvotes: 2

Related Questions