CODEWITHSUNDEEP
mysqlcsvload-data-infile
EmmyS
EmmyS

Reputation: 12138

How to import CSV file that doesn't have data for every column into mySQL

I'm trying to import a CSV file into MySQL (using Workbench) using the query found here:

LOAD DATA INFILE 'badBDay.csv' 
INTO TABLE user_birthdays 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
(username, user_birthday)

The problem is, there are rows in the file that do not have data for both columns (i.e. some are missing a birthday.)

Here's the table CREATE statement that MySQL exported:

CREATE TABLE `user_birthdays` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(150) DEFAULT NULL,
  `user_birthday` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=65536 DEFAULT CHARSET=utf8$$

How can I load my file in if there are records that don't contain a value for all columns?

UPDATE

Here are a few sample rows; I did find that there was one row that was completely blank; I removed that and the error goes away, but the table still doesn't seem to be populating at all. (And yes, in some cases the username is numeric; this is just from the top of the alpha-sorted file. There are more that have "real" usernames.)

109,12/24/1979
124,03/31/1987
142,11/30/1965
143,
233,11/03/1970
311,
321,07/08/1971

Upvotes: 2

Views: 6920

Answers (1)

peterm
peterm

Reputation: 92785

You have to make sure that there is no blank rows in file and even if a value is missing for a field the delimiter (comma in your case) is still there.

Now if a file was produced on Windows machine you might need to change lines terminator to \r\n

LOAD DATA INFILE 'badBDay.csv' 
INTO TABLE user_birthdays 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\r\n' 
(username, user_birthday)

Upvotes: 3

Related Questions