PHP, CSV and MySQL import problem :/

[Nickmadd]

Hey guys, I'm having a nightmare at the moment.

 

I am trying to import a CSV file into a MySQL table, some of the data is interested properly into the rows however some of the CSV row do not get imported.

 

Here is my PHP script:

<?php

$databasehost = "localhost";
$databasename = "name";
$databasetable = "import";
$databaseusername="username";
$databasepassword = "password";
$fieldseparator = ",";
$lineseparator = "\n";
$enclosedbyquote = '"';
$csvfile = "db-core/feed/csv/test.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());
}

$pdo->exec("TRUNCATE TABLE `$databasetable`");

$affectedRows = $pdo->exec("
LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." REPLACE INTO TABLE `$databasetable`
FIELDS OPTIONALLY ENCLOSED BY ".$pdo->quote($enclosedbyquote)."
TERMINATED BY ".$pdo->quote($fieldseparator)."
LINES TERMINATED BY ".$pdo->quote($lineseparator)."
IGNORE 1 LINES");

echo "Loaded a total of $affectedRows records from this csv file.\n";

?>

As you can see the script is importing the CSV into the table however only 40 of the rows are added to the SQL table when there are actually 80 rows in the CSV. I am also telling the script to IGNORE 1 LINES if that has anything to do with the issue.

 

Here is one of the rows from the CSV file:

"AETA83919","AETV34458584","SD57VUP","BLACK","Diesel","2007","76778","Hatchback","3","VAUXHALL","CORSA","1.3 CDTi [90] SXi 3dr [AC]","1200","3989","","Manual","http://images.autoexposure.co.uk/AETA83919/AETV34458584_1b.jpg","0","3","","0","2007 57 reg VAUXHALL CORSA 1.3 CDTi [90] SXi 3dr [AC], Manual, Hatchback, Diesel, 76,778 miles. BLACK, Comprehensive Warranty Included In Price. HPI Checked! Pre Delivery Health Check!, 5 seats, LOVELY VAUXHALL CORSA SXI 1.3CDTI SXI WITH AIR CONDITIONING!! VERY WELL CARED FOR AND MAINTAINED AND JUST SERVICED BY DRIVEN CAR SALES LTD AT 76K!! GREAT COMBINATION OF PERFORMANCE AND ECONOMY!! VIEWING AND TEST DRIVING IS RECOMMENDED!! EACH CAR COMES WITH A MININIUM 9 MONTHS MOT. FULL MECHANICAL HEALTH CHECK GUARANTEES CAR IS PREPARED TO THE HIGHEST STANDARD. COMPREHENSIVE RAC WARRANTY INCLUDED. DRIVEN CAR SALES WELCOME ANY INSPECTION. BUY WITH CONFIDENCE AND REASSURANCE AS DRIVEN CAR SALES LTD ARE OFFICIAL MEMBERS OF RETAIL MOTOR INDUSTRY FEDERATION. VIEW OUR REVIEWS ON TRUSTPILOT. 3,989","","0","1","","UK","0","","0","0","","","",""

As far as I can see everything looks fine with the row, all of the rows in the CSV end like this.

 

One thing I have noticed is it looks like the rows that are missing are in fact being placed in the last column of every other row here is an example:

 

 

 

 

So as you can see it looks like one row is being inserted into the last column of some rows and I am so confused to why this might be happening?

 

Any ideas?

 

 

 

[mac_gyver]

it's likely that your new-lines in the csv file doesn't match $lineseparator = "\n"; where/how is the csv being produced and are you editing it at any point, where your editor could be modifying the new-lines?

[Nickmadd]

I don't edit the CSV file, I've been stuck on this issue all day.

 

Here is the CSV file if anyone can take a quick look?

 

http://www.drivencarsales.co.uk/db-core/feed/csv/DrivenCarSales.csv

 

It would be much appreciated,

[mac_gyver]

posting a sql dump of just your table definition, would help someone to recreate/debug the problem.

[Barand]

this will create an import table and load your csv

$db = new mysqli(HOST,USERNAME,PASSWORD,'test');

$fp = fopen('DrivenCarSales.csv','r');

$k = 0;
$sizes=array_fill(0,35,0);
$heads = fgetcsv($fp,2048);     // get header row

while ($line = fgetcsv($fp,2048)) {
    // get max field lengths
    for ($i=0; $i<35; $i++) {
        $sizes[$i] = max($sizes[$i], strlen($line[$i]));
    }
}
fclose($fp);

$fields = array_combine($heads,$sizes);

    // drop table if already exists
$db->query("DROP TABLE IF EXISTS import");

    // create the import table
$sql = "CREATE TABLE `import` (\n";
foreach ($fields as $fn=>$sz) {
    $sz+=2;
    $sql .= "`$fn` VARCHAR($sz),\n";
}
$sql .= "PRIMARY KEY (Vehicle_ID)\n)";
$db->query($sql);

//
// now load the data
//
$fp = fopen('DrivenCarSales.csv','r');
$heads = fgetcsv($fp,2048);     // get header row

while ($line = fgetcsv($fp,2048)) {
    $line = array_map(array($db,'real_escape_string'), $line);
    $data = join("','", $line);
    $db->query("INSERT IGNORE INTO `import` VALUES ('$data')") or die($db->error);
}
fclose($fp);

edit: PS your pictureRefs and options fields need normalising. Your field name "condition" is a reserved SQL word

Edited September 7, 2014 by Barand

[nik_jain]

You could try replacing \n by \r\n as the line separator. As thats what the csv contains.