Issue with importing CSV using PHP due to commas in string

Question

I am trying to import a CSV file into a MySql db and its working except for one thing - one of the fields comes from a free form text field and it causes issues when users input commas. There is however a " at the beginning and the end of the data in this one field. I am using the code below to try to change it to an empty field but I cant get it to work - I must be missing something and staring at it for a few hours straight isnt helping. Thanks in advance!!

Code:

<?php
    require('phpsqlajax_dbinfo.php');
    $databasehost = $db_host; 
    $databasename = $db_name; 
    $databasetable = "tbl_csvImport"; 
    $databaseusername=$db_user; 
    $databasepassword = $db_pass; 
    $fieldseparator = ","; 
    $lineseparator = "\n";
    $csvfile = "doecsv.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());
    }

    $affectedRows = $pdo->exec("
      LOAD DATA LOCAL INFILE ".preg_replace('/"[^"]+"/','',$pdo->quote($csvfile))." INTO TABLE `$databasetable`
        FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)."
        LINES TERMINATED BY ".$pdo->quote($lineseparator))

?>

gen_Eric · Accepted Answer · 2014-02-14 19:20:25Z

2

You need to tell MySQL that the fields may be enclosed in quotes.

LOAD DATA LOCAL INFILE 'yourFile.csv' INTO TABLE `yourTable`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'

answered Feb 14, 2014 at 19:20

gen_Eric

228k42 gold badges303 silver badges343 bronze badges

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

Collectives™ on Stack Overflow

Issue with importing CSV using PHP due to commas in string

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related