Importing CSV to MySQL problem

[Nickmadd]

Hey guys! So I have this script to import a CSV file into my database:

<?php
$databasehost = "localhost"; 
$databasename = "import"; 
$databasetable = "import"; 
$databaseusername="username"; 
$databasepassword = "password"; 
$fieldseparator = ","; 
$lineseparator = "\n";
$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`
	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";

?>

So that script basically imports a csv replacing all of the other data.

 

The issue I am having is that the data inside one of the columns contains commas, they are wrapped in quotations.

 

Here is an example: 

 

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

 

How could I make the script determine the text within those quotation marks as a whole value instead of separating them into individual columns?

 

Thanks any help on this would be great!

[mac_gyver]

you would add an ENCLOSED BY ... term to the query.

[Nickmadd]

you would add an ENCLOSED BY ... term to the query.

 

So far I have:

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

Does the ".$pdo->quote($fieldseparator)." part of the script need changing? I'm guessing it does but what would insert? Sorry I am still very nooby.

[Barand]

At the moment you are effectively specifying

ENCLOSED BY ","

You need

ENCLOSED BY '"'

[Nickmadd]

 

At the moment you are effectively specifying

ENCLOSED BY ","

You need

ENCLOSED BY '"'

 

Right so I have tried adding this:

<?php
$databasehost = "localhost"; 
$databasename = "dealerby_vehimport"; 
$databasetable = "import"; 
$databaseusername="dealerby_veh"; 
$databasepassword = "Millston31"; 
$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";

?>

I am now getting an error when the script is ran, I have added:

$enclosedbyquote = '"';

And 

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

However this results in an error any idea why?

 

Thanks

[Barand]

If you printed it out you would have

ENCLOSED BY "

instead of

ENCLOSED BY '"'

edit: and the same goes for the other characters you specified

Edited July 16, 2014 by Barand

[Nickmadd]

If you printed it out you would have

ENCLOSED BY "

instead of

ENCLOSED BY '"'

edit: and the same goes for the other characters you specified

 

 

Okay so that should work shouldn't it? The CSV value's are enclosed by a quotation marks?

[maxxd]

You're also going to want to edit your post to remove the database credentials. Won't help your problem (listening to Barand will do that), but it will certainly increase your db security.

[Barand]

Okay so that should work shouldn't it? The CSV value's are enclosed by a quotation marks?

Build your query query as a separate string prior to execution. Then you can echo it to see how it looks.

[Stefany93]

If you are building the CSV files, then simply replace the delimeter with another character like |  for example.

If the CSV files are handed to you, then again replace the commas with | and that should solve your problem