Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

nicholas maddren
nicholas maddren
12,793 Points
Posted by nicholas maddren
nicholas maddren
12,793 Points

Importing CSV to MySQL problem

Hey guys! So I've made 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!

1 Answer

Jeremy Menicucci
Jeremy Menicucci
19,227 Points
Jeremy Menicucci
Jeremy Menicucci
19,227 Points

If I'm understanding correctly what you're looking for, than I think you would need to use "ENCLOSED BY" and set that to double quotes. So maybe declare a variable and adjust your exec like so, 

<?php
$databasehost = "localhost"; 
$databasename = "import"; 
$databasetable = "import"; 
$databaseusername="username"; 
$databasepassword = "password"; 
$fieldseparator = ","; 
$lineseparator = "\n";

$enclosedby = "\"";

$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)." 
    ENCLOSED BY ".$pdo->quote($enclosed)." 
    LINES TERMINATED BY ".$pdo->quote($lineseparator)."
    IGNORE 1 LINES");

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

?>

If that doesn't work I would probably need to see your database structure and the full csv file. Let me know!