LOAD DATA INFILE only Loads 0s, NULLs, and Blanks

Question

I am trying to load a .csv file into a mysql database using the following code:

LOAD DATA LOCAL INFILE 'c:/mytestfile.csv' INTO TABLE report.test
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
(ONE, TWO)

The .csv file looks like:

ONE,TWO
1,2

And the two columns in report.test are both integers.

When I run the SQL code, I get the the both values in the first row of report.test are 0s. When I run this on my actual .csv file, only a fraction of the rows are read, and they are populated by 0s, NULLs, and Blanks.

Am I missing a setting somewhere?

---

UPDATE 1 - I dont have enough reputation to post pictures, so I had to do links.

Table Structure

Example File

---

UPDATE 2

When I use:

LINES TERMINATED BY '\r\n'

The warnings are :

'Warning','1366','Incorrect integer value: \'??\' for column \'ONE\' at row 1'
'Warning','1366','Incorrect integer value: \'??\' for column \'TWO\' at row 1'
'Warning','1262','Row 1 was truncated; it contained more data than there were input columns'

When I use:

LINES TERMINATED BY '\n'

The warnings are :

'Warning','1366','Incorrect integer value: \'??\' for column \'ONE\' at row 1'
'Warning','1366','Incorrect integer value: \'??\' for column \'TWO\' at row 1'
'Warning','1262','Row 1 was truncated; it contained more data than there were input columns'
'Warning','1366','Incorrect integer value: \'?\' for column \'ONE\' at row 2'
'Warning','1261','Row 2 doesn\'t contain data for all columns'

Answer 1

The SQL statement looks fine.

It adds 0s in the first row because you're adding the column headers too. To ignore the first line use IGNORE 1 LINES.

The only reason I can think of why you're having problems is because of the file that doesn't have carriage return.

Please test with the following statement:

LOAD DATA LOCAL INFILE 'c:/mytestfile.csv' INTO TABLE report.test
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(ONE, TWO)