I’m trying to import data from google sheets to mysql. I saved the google sheet as .csv. One of the columns square_feet has very few entries so there are a lot of blank cells. I am unable to import any row which has those blank cells. The error I’m getting is.
INSERT INTO `airbnb` VALUES ('2265', 'Austin', 'TX', '78702', 'Austin', 'United States', 'House', 'Entire home/apt', '4', '', '$225.00', '$100.00')
#1366 – Incorrect integer value: ” for column ‘square_feet’ at row 1
The column square_feet when created was given data type int not null, no default value assigned.
Advertisement
Answer
Because you are specifying ” as the value, no default value would be used even if there was one specified.
You are getting the error because ‘STRICT_TRANS_TABLES’ mode is enabled, which in essence tries to ensure that the values you think you are setting are what you actually are going to get.
If you disable it for the session, with:
set @@sql_mode=replace(@@sql_mode,'STRICT_TRANS_TABLES','');
your insert will succeed, and set square_feet to 0.
If you need to affect a different session, you can set it globally with:
set global sql_mode=replace(@@sql_mode,'STRICT_TRANS_TABLES','');
(which will last until the database server is restarted) or change it in the mysql config file.