Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

mysql

csv

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?

like image 531
Joe Mornin Avatar asked Mar 22 '11 16:03

Joe Mornin


People also ask

What is the default value of column for which no default value is defined?

Using NULL as a default value If you specify no default value for a column, the default is NULL unless you place a NOT NULL constraint on the column. In this case, no default value exists. If you specify NULL as the default value for a column, you cannot specify a NOT NULL constraint as part of the column definition.

How do I import a CSV file into MySQL?

In the Format list, select CSV. Changing format-specific options. If the csv file is delimited by a character other than a comma or if there are other specifications to the csv files, we can change it in this portion. Click Go to start importing the csv file and the data will be successfully imported into MySQL.

Which statement is used to import a CSV file in MySQL?

The LOAD DATA INFILE statement also allows us to import CSV file form client (local system) to a remote MySQL Server. When we add the LOCAL clause in the LOAD DATA INFILE statement, the client program can read the CSV file on the local system and sends it to the MySQL database server.

How do I insert selected columns from a CSV file to a MySQL database using load data infile?

The code is like this: LOAD DATA INFILE '/path/filename. csv' INTO TABLE table_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (column_name3, column_name5); Here you go with adding data to only two columns(you can choose them with the name of the column) to the table.


2 Answers

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);
like image 161
Ike Walker Avatar answered Sep 27 '22 18:09

Ike Walker


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

like image 27
Spring Avatar answered Sep 27 '22 16:09

Spring