08-13-16, 18:36 #1Registered User
Provided Answers: 1
- Join Date
- Sep 2002
- Montreal, Canada
Unanswered: Problem with missing values in CSV file during LOAD DATA
I'm trying to import a little stupid CSV file with some missing values.
Here's the table definition:
CREATE TABLE `ds_world_cities` (
`country` CHAR(2) NULL DEFAULT NULL,
`city` VARCHAR(200) NULL DEFAULT NULL,
`accent_city` VARCHAR(200) NULL DEFAULT NULL,
`region` VARCHAR(80) NULL DEFAULT NULL,
`population` INT(11) UNSIGNED NULL DEFAULT NULL,
`latitude` DECIMAL(11,7) NULL DEFAULT NULL,
`longitude` DECIMAL(11,7) NULL DEFAULT NULL
COMMENT='This is a test'
Here's the data I am trying to load (just copy the text and save as CSV file):
ad,andorra la vella,Andorra la Vella,07,20430,42.5,1.5166667
Here's my LOAD DATA statement:
LOAD DATA LOW_PRIORITY LOCAL
REPLACE INTO TABLE `lerxst`.`ds_world_cities`
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n' (`country`, `city`, `accent_city`, `region`, `population`, `latitude`, `longitude`);
MySQL stops the import on line one because of the following error:
SQL Error (1366): Incorrect integer value: 'ad' for column 'id' at row 1
Am I missing something? It's the very first time ever I am having so much trouble importing a CSV file! I had 10000+ tables on a 5.5 server and 90% of those tables were created by CSV files (99% of which had missing values of some kind in a bunch of records!).
Q1: what has changed in 5.7?
Q2: editing or writing a script to "fix" the CSV files is not an option, how can I import (like I used to do sooooooo easily in 5.5) without altering the files?
Q3: the import doesn't work with the MySQL client nor HeidiSQL
My SQL_MODE is currently STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION but I have tried many variations and it never works
08-22-16, 06:21 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
i would expect string/text/char values to be delimited in a csv fileI'd rather be riding on the Tiger 800 or the Norton