Importing csv file with null values into phpmyadmin

Question

When I import a csv file into MySQL (phpmyadmin), for all integer values that are not specified in the file but have a default of null there is an error message: #1366 - Incorrect integer value: '' for column 'id' at row 1.. I have these questions:

a. How do I import a csv file that does not have the row-id specified if the DB table has that id defined as auto-increment?

b. What do I need in the csv file or in the table column specification in phpmyadmin for integer column that have a default of null?

Here is are sample rows from the csv file.

id,year,month,date,day,description,ranking
,,3,1,,,
,,3,2,,,
,,3,3,,,
,,3,4,,,
,,3,5,,,
,,3,6,,,
,,3,7,,,
,,3,7,,"Saints Perpetua and Felicity, Martyrs",
,,3,8,,,
,,3,8,,"Saint John of God, Religious",
,,3,9,,,
,,3,9,,"Saint Frances of Rome, Religious",
,,3,10,,,

The columns that cause the error are id, year, ranking. They are all integer columns. The column id is auto increment. The other columns are INT(11) with a default of NULL. Thanks.

Marc B · Accepted Answer · 2012-02-14 18:29:20Z

1

CSV has no concept of "Nulls". It's impossible to differentiate between a field that is null, and a field that has a legitimately empty value (e.g. empty string). You'll have to massage the rows as you load them prior to query insertion, to replace any 'empty strings' with appropriate NULLs

e.g.

$row = fgetcsv(...);
$row[0] = 'NULL';

answered Feb 14, 2012 at 18:29

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

1 Comment

user823527 Over a year ago

I added NULL for each blank field in the csv and this worked.

Collectives™ on Stack Overflow

Importing csv file with null values into phpmyadmin

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related