I am using the below code to import a huge csv file.(almost 42452 lines):
<?php
$databasehost = "localhost";
$databasename = "framework_db";
$databasetable = "Main_CSV_Table";
$databaseusername="root";
$databasepassword = "";
$fieldseparator = ",";
$lineseparator = "";
$csvfile = "main.csv";
if(!file_exists($csvfile)) {
die("File not found. Make sure you specified the correct path.");
}
try {
$pdo = new PDO("mysql:host=$databasehost;dbname=$databasename",
$databaseusername, $databasepassword,
array(
PDO::MYSQL_ATTR_LOCAL_INFILE => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
)
);
} catch (PDOException $e) {
die("database connection failed: ".$e->getMessage());
}
$affectedRows = $pdo->exec("
LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)."
INTO TABLE `$databasetable`
FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)."
LINES TERMINATED BY ".$pdo->quote($lineseparator)."
IGNORE 1 LINES
(Code,Name,Trx,DocType,Date,Period,Ref,Description,Category,Dr,Cr)
;");
echo "Loaded a total of $affectedRows records from this csv file.\n";
?>
But when I import this csv to mysql, sometimes the data is inserted in wrong columns like below:
Also there is an another issue. When I import the file of 42452 lines, it only imports 38777 lines.(upload_max_filesize = 500M is in php.ini) I would be grateful if someone could help me on this problem? Thanks in advance!