2

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
Community
  • 1
  • 1
EmmyS
  • 11,892
  • 48
  • 101
  • 156
  • Do you have commas in place in your file even if a row doesn't have birthday value? Please show a few sample rows from your file. – peterm Aug 27 '13 at 21:22
  • Yes. But it's still giving me an error when I try to run it, telling me that certain rows don't have data for all columns. Adding sample rows to OP in a sec. – EmmyS Aug 27 '13 at 21:26
  • Ok. What platform you're on (Windows or Unix)? Or more importantly on which platform the file was produced? – peterm Aug 27 '13 at 21:35
  • Both are Windows. I did change the line endings, per your answer below. Didn't make a difference. There's no indication that it's even running - no error, but no success message either. The table remains stubbornly empty. – EmmyS Aug 27 '13 at 21:42
  • @EmmS Did you manage to solve this problem as I'm getting similar problem. – Gregory William Bryant Mar 19 '14 at 11:03
  • @GregoryWilliamBryant - I did at some point, but it was more than six months ago, and since it was a one-off, I honestly don't remember how I did it; sorry. – EmmyS Mar 19 '14 at 11:54
  • @EmmS, I solved my problem, my problem was down to unnecessary spaces between my field deliminators and field containers. I.e "" , "" was used instead of the correct "","" for blank fields. I was containing all my fields in quotes as some of my fields had commas in them. – Gregory William Bryant Mar 21 '14 at 11:35

1 Answers1

3

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)
peterm
  • 91,357
  • 15
  • 148
  • 157
  • As I said in the addition to the OP, I did remove the blank rows, and the delimiter is there in rows with incomplete data. It is a Windows machine, so I'll try changing the terminator. – EmmyS Aug 27 '13 at 21:43
  • Nope, that didn't do it. No errors, but it gives no indication of running at all and the table is still empty. – EmmyS Aug 27 '13 at 21:45
  • Try running the LOAD DATA INFILE from the MySQL command line. By recreating the scene in my local environment, I can load the file without problem. – wchiquito Aug 27 '13 at 23:03