CODEWITHSUNDEEP
phpmysqlcsv
Nick M
Nick M

Reputation: 85

ENCLOSED BY error whilst using PHP CSV import script

I have CSV file that needs importing to a MySQL database. I am using a PHP script to import the CSV.

The script works fine until I add an ENCLOSED BY statement, the reason I am adding that statement is because one of the values in the CSV column contains text with commas however the value is wrapped by quotation marks.

Here is an example of the CSV column that contains the value wrapped in quotation marks:

,"1 Registered Keeper, Full Service History, Central Locking, Electric Windows, Electric Mirrors, ABS, Traction Control, Climate Control, Power Steering, Drivers Airbag, Passenger Airbag, Side Airbags, Cruise Control, Alarm, Immobiliser, Half Leather Interior, Alloy Wheels",

Here is the script I have tried to get working:

<?php
$databasehost = "localhost"; 
$databasename = "import"; 
$databasetable = "import"; 
$databaseusername="import"; 
$databasepassword = "password"; 
$fieldseparator = ","; 
$lineseparator = "\n";
$enclosedbyquote = '"';
$csvfile = "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`
    ENCLOSED BY ".$pdo->quote($enclosedbyquote)."
    FIELDS 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";

?>

Since I have added:

$enclosedbyquote = '"';

And

ENCLOSED BY ".$pdo->quote($enclosedbyquote)."

I get an error :(

Any idea what I am doing wrong, any tips would be much appreciated!?

Upvotes: 0

Views: 543

Answers (2)

Marcin Nabiałek
Marcin Nabiałek

Reputation: 111899

You should look at Syntax. You need tu put FIELDS word first.

Instead of

ENCLOSED BY ".$pdo->quote($enclosedbyquote)."
    FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)."

you should have:

FIELDS ENCLOSED BY ".$pdo->quote($enclosedbyquote)."
     TERMINATED BY ".$pdo->quote($fieldseparator)."

Upvotes: 1

JochenJung
JochenJung

Reputation: 7213

Looks like you are missing quotes arround your enclosed by char:

ENCLOSED BY '".$pdo->quote($enclosedbyquote)."'

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Upvotes: 0

Related Questions