ENCLOSED BY error whilst using PHP CSV import script

Question

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!?

Not all fields are enclosed, so you should use ´OPTIONALLY ENCLOSED BY´ instead. — VMai
– VMai, Commented Jul 17, 2014 at 11:57

Marcin Nabiałek · Accepted Answer · 2014-07-17 11:58:55Z

1

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)."

answered Jul 17, 2014 at 11:58

Marcin Nabiałek

112k43 gold badges271 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Nick M Over a year ago

I am now left with this error :( Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\"'' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES' at line 2' in /home/dealerby/public_html/driven_import.php:35 Stack trace: #0 /home/dealerby/public_html/driven_import.php(35): PDO->exec('??LOAD DATA LOC...') #1 {main} thrown in /home/dealerby/public_html/driven_import.php on line 35

Marcin Nabiałek Over a year ago

So what's your query now? Have you removed enclosed by ? I don't see it in this query

Nick M Over a year ago

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

Marcin Nabiałek Over a year ago

Don't use FIELDS 2 times. They should be only used one time as FIELDS OPTIONALLY ENCLOSED BY ... TERMINATED BY ... and not FIELDS OPTIONALLY ENCLOSED BY ... FIELDS TERMINATED BY ...

JochenJung · Accepted Answer · 2014-07-17 11:57:13Z

0

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

answered Jul 17, 2014 at 11:57

JochenJung

7,24312 gold badges67 silver badges114 bronze badges

Collectives™ on Stack Overflow

ENCLOSED BY error whilst using PHP CSV import script

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related