Geek Answers Handbook

The default value for empty integer fields when importing CSV data into MySQL

I am importing CSV into a MySQL table with LOAD DATA INFILE. One of the table fields contains zip code data that I defined in the table structure contributor_zipcode INT.

In CSV, this field is sometimes empty. When I execute the query LOAD, MySQL will raise a warning, for example:

Incorrect integer value: '' for column 'contributor_zipcode' at row 180

I tried to override the field as contributor_zipcode INT DEFAULT '0', which creates the same warning, and contributor_zipcode INT DEFAULT NULLwhich MySQL will not allow. Any tips?

+5
source share
3 answers

Null values ​​are interpreted as an empty string (''), not NULL, so the default value is not used.

, , .

, , (NULL, 0 ..).

, 0:

LOAD DATA INFILE '...'
INTO TABLE your_table
FIELDS TERMINATED BY ','
(column_one,..., @contributor_zipcode,..., column_n)
SET contributor_zipcode = IF(@contributor_zipcode='',0,@contributor_zipcode);
+10

Csv "" \n", script, db

+1

(, zip-) null, 0.

, ( ) People.

create table People (
    id int,
    first_name varchar(30),
    last_name varchar(50),
    city varchar(50),
    state varchar(50),
    zip int
);

Then upload file.csvto the table People. Pay attention to the last line (starting from set), where it zipis assigned nullif the value in the file is equal ""(also like an empty line)

load data local infile '/path/to/file.csv'
into table People
fields 
    terminated by ','
    enclosed by '"'
lines terminated by '\n'
(id, first_name, last_name, city, state, @zip)
set zip = if(@zip='', null, @zip);
0
source

More articles:


All Articles