Help with Load Data Infile in MySQL

[igowerf]

I'm trying to load a text file that contains just one column of data. The file was made in Windows so each line is terminted by '\r\n'. I verified this by looking at the file in a hex editor. I'm using Load Data to get it into my MySQL database, but it's not working. Here's what I'm entering:

LOAD DATA INFILE 'test.csv' INTO TABLE PHONE_NUMBERS LINES TERMINATED BY '\r\n';

It'll say duplicate entry at key 1. Is my syntax incorrect?

My friend wrote a C++ program that reads in the file line by line and then prints it out with a \n, but it stills adds the \r for some reason. If I change it to tabs, I get the same error in MySQL (lines terminated by '\t' doesn't work). Any ideas?

 

[Barnaby W. Füi]

It might be totally unrelated to the line endings. But you definitely don't need to write a c++ program to convert them, you can do it a million different ways. Filtering through the command tr -d '\r' is probably the easiest (there are dedicated utilities to do it, but you're pretty much guaranteed to have tr on the machine).

What does your mysql table look like?

 

[igowerf]

The table just has a single column (unsigned int) as the primary key. What's tr? This is a Windows NT system, btw.