Default value for empty integer fields when importing CSV data in MySQL

Question

I'm importing a CSV into a MySQL table with LOAD DATA INFILE. One of the table's fields stores zip code data, which I've defined in the table structure contributor_zipcode INT.

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

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

I've tried redefining the field as contributor_zipcode INT DEFAULT '0', which creates the same warning, and contributor_zipcode INT DEFAULT NULL, which MySQL won't allow. Any advice?

2004,urn:fec:transaction,indiv:2004:1902928,15,24020610238,f,1000.00,2004-06-15,"FELDSTEIN, MARTIN",h30013132661,I,HARVARD UNIVERSITY,,M,,BELMONT,MA,02478,H5100,Harvard University,,,,Lisa Murkowski (R),N00026050,R,P,AK,AK,,Lisa Murkowski - US Senate,C00384529,R,f,,,federal:senate,federal:senate,I,W — Joe Mornin
– Joe Mornin, Commented Mar 22, 2011 at 16:17

Ike Walker · Accepted Answer · 2011-03-22 16:35:18Z

13

The empty values are being interpreted as the empty string (''), not NULL, so the default value is not being used.

If you want to explicitly control the handling of these empty strings, the best thing to do is to load them into a user variable, and then set the column conditionally using the user variable.

You could use this to set the value to whatever you want (NULL, 0, etc.).

Here's an example, assuming you want to set it to 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);

answered Mar 22, 2011 at 16:35

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joe Mornin Over a year ago

Thanks, this is really helpful. One follow-up: will MySQL allow me to set the value of an INT field to NULL? Or would you recommend just using 0?

Ike Walker Over a year ago

Yes, you can set an INT to NULL, which is probably what you want to do in this case. You just need to make sure the column is defined to allow NULLs.

Spring · Accepted Answer · 2014-04-23 15:29:14Z

1

In Csv I replaced all the "" with "\N" before I run the script, this creates a null field in db

answered Apr 23, 2014 at 15:29

Spring

11.9k30 gold badges123 silver badges193 bronze badges

Comments

openwonk · Accepted Answer · 2016-10-16 04:21:55Z

0

For some elements (e.g. zip codes), may be better for default value to be null, not 0.

Assume you have a table of people called (what else) People.

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

Next, load your file.csv into the People table. Note the last line (starting with set) where zip is assigned null if the value in the file is "" (aka an empty string)

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);

answered Oct 16, 2016 at 4:21

openwonk

15.8k7 gold badges50 silver badges41 bronze badges

Collectives™ on Stack Overflow

Default value for empty integer fields when importing CSV data in MySQL

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related