LOAD DATA LOCAL INFILE only enters 5 line

Question

I have a csv file and I am using LOAD DATA LOCAL INFILE command to load it inside mysql, but I've got no idea why it only reads 5 lines of it. Here is my php code:

<?php
include "../config.php";
$conn = new mysqli( $servername, $username, $password, $dbname );
if ( $conn->connect_error ) {
    die( "Connection failed: " . $conn->connect_error );
} //$conn->connect_error

$sql= "LOAD DATA LOCAL INFILE 'uploads/cell.csv'
INTO TABLE table3
CHARACTER SET UTF8
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'";
$result           = $conn->query( $sql );
echo("Error description: " . mysqli_error($conn));
?>

and the csv file is attached in link below.

cell.csv

Mysql after executing the php code looks like this:

You should delete the header lines in that CSV file. Just leave the values in the table and try again. You can tell MySQL to omit some rows also. Take a look at http://stackoverflow.com/questions/14127529/mysql-import-data-from-csv-using-load-data-infile — valicu2000, Oct 17 '16 at 13:02
i delete more than 10 rows of it but it adds nothing when i deleted those — Sina Nouri, Oct 17 '16 at 13:10
I see that some fields have multiple commas and it breaks the structure. In your query use: FIELDS TERMINATED BY ',' ENCLOSED BY '"' instead of FIELDS TERMINATED BY ',' . You can read this tutorial: http://www.mysqltutorial.org/import-csv-file-mysql-table/ . Also I'n not sure if lines are terminated by \r\n or just \n . — valicu2000, Oct 17 '16 at 13:18

score 0 · Answer 1 · answered Oct 17 '16 at 13:23

I finally figured it out by myself, the problem was /r/n, somr of lines finish with /r and some finish with /r/n, i chenged the code to:

<?php
include "../config.php";
$conn = new mysqli( $servername, $username, $password, $dbname );
if ( $conn->connect_error ) {
    die( "Connection failed: " . $conn->connect_error );
} //$conn->connect_error

$sql= "LOAD DATA LOCAL INFILE 'uploads/cell.csv'
INTO TABLE table3
CHARACTER SET UTF8
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r'";
$result           = $conn->query( $sql );
echo("Error description: " . mysqli_error($conn));
?>

and it fixed the problem.

LOAD DATA LOCAL INFILE only enters 5 line

1 Answers1