Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    222
    Provided Answers: 1

    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'
    COLLATE='utf8mb4_general_ci'
    ENGINE=InnoDB
    ;

    Here's the data I am trying to load (just copy the text and save as CSV file):

    ad,aixas,Aixas,06,,42.4833333,1.4666667
    ad,aixirivali,Aixirivali,06,,42.4666667,1.5
    ad,aixirivall,Aixirivall,06,,42.4666667,1.5
    ad,aixirvall,Aixirvall,06,,42.4666667,1.5
    ad,aixovall,Aixovall,06,,42.4666667,1.4833333
    ad,andorra,Andorra,07,,42.5,1.5166667
    ad,andorra la vella,Andorra la Vella,07,20430,42.5,1.5166667
    ad,andorra-vieille,Andorra-Vieille,07,,42.5,1.5166667
    ad,andorre,Andorre,07,,42.5,1.5166667
    ad,andorre-la-vieille,Andorre-la-Vieille,07,,42.5,1.5166667
    ad,andorre-vieille,Andorre-Vieille,07,,42.5,1.5166667
    ad,ansalonga,Ansalonga,04,,42.5666667,1.5166667

    Here's my LOAD DATA statement:

    LOAD DATA LOW_PRIORITY LOCAL
    INFILE 'C:\\Recv\\testimport.csv'
    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

    tia

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    i would expect string/text/char values to be delimited in a csv file
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •