1

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";

?>

Table Structre: enter image description here

But when I import this csv to mysql, sometimes the data is inserted in wrong columns like below: enter image description here

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!

UPDATE CSV File is give below: enter image description here

Zendie
  • 1,180
  • 1
  • 12
  • 27
  • 1
    It's hard to say what is wrong, but based on the table screenshot my guess is that your CSV file is irregular, e.g. has missing data, delimiters, etc. As for the hard stop at 38777 lines, maybe you need to increase your upload max size. How big is this CSV file? – Tim Biegeleisen Feb 03 '17 at 10:16
  • Look at this part of your CSV it has invalid structure: `42912.1 3500` and `3901.1,0 7560`, So my question is: Why is the float seperated by an komma to the next number, but in the first example not? – JustOnUnderMillions Feb 03 '17 at 10:16
  • May be your csv file is corrupt or broken, i recomand you to export it to excel (use google docs for better results), and see if you get the 42452 lines, if not, in think you have to do repair it manually – beta-developper Feb 03 '17 at 10:18
  • By the way your csv example is not right formated, normaly after `,Category,Dr,Cr` the next data should be in the next line. Not so good for checking your issue. – JustOnUnderMillions Feb 03 '17 at 10:18
  • @TimBiegeleisen the csv file size is 4.4MB only – Zendie Feb 03 '17 at 10:29
  • I would say the problem is your data. You can't expect MySQL to swallow this, so a better approach would be to fix your data at the source. – Tim Biegeleisen Feb 03 '17 at 10:30
  • @JustOnUnderMillions I have updated the question with photo of csv file. This editor put the wrong format – Zendie Feb 03 '17 at 10:56
  • And what are your line and field seperators? – JustOnUnderMillions Feb 03 '17 at 11:08
  • field separator is "," . and line separator is "". when i put "\n" it doesnt import – Zendie Feb 03 '17 at 11:16
  • How about `\r\n`? – JustOnUnderMillions Feb 03 '17 at 11:18
  • same with \r\n. zero files imported – Zendie Feb 03 '17 at 11:21
  • Maybe you should try to iterate over the csv with `fgetcsv` http://php.net/manual/en/function.fgetcsv.php to check if the csv is valid and works as you expect. For me its a little hard to help more. Only ting that comes in mind currnetly: encoding issue. What encoding has the CSV? http://stackoverflow.com/questions/4221176/excel-to-csv-with-utf8-encoding – JustOnUnderMillions Feb 03 '17 at 11:24
  • http://stackoverflow.com/questions/14127529/mysql-import-data-from-csv-using-load-data-infile – JustOnUnderMillions Feb 03 '17 at 11:26
  • ok. Thank you. I will have a look – Zendie Feb 03 '17 at 12:10

0 Answers0