import .CSV file with PHP using PDO

Question

I'm having a problem importing .CSV data into a SQL database. I'm trying to use PDO in my PHP file to accomplish this and I can't seem to figure this out.

if (isset($_FILES['uploadedfile'])) {   

    // get the csv file and open it up
    $file = $_FILES['uploadedfile']['tmp_name']; 
    $handle = fopen($file, "r"); 
    try { 
        // prepare for insertion
        $query_ip = $db->prepare('
                INSERT INTO projects (                                      
                    id, project_name, contact, pm, apm, 
                    est_start, est_end, trips, tasks, perc_complete, 
                    bcwp, actual, cpi, bcws, bac, 
                    comments, status, project_revenue, profit_margin, pm_perc, 
                    audited, account_id
                ) VALUES (
                    ?, ?, ?, ?, ?,
                    ?, ?, ?, ?, ?, 
                    ?, ?, ?, ?, ?,
                    ?, ?, ?, ?, ?, 
                    ?, ?            
                )');                                         
        $data = fgetcsv($handle,1000,",","'");
        $query_ip->execute($data);
        $count = $query_ip->rowCount(); 
        fclose($handle);

    } catch(PDOException $e) {
        die($e->getMessage());
    }       

    echo 'Projects imported ' . $count . ' rows were affected';

} else {
    echo 'Could not import projects';
}

Now this works, kind of. It imports the data but as you may have guessed this is only inserting the first row of the .CSV file, which by the way is the column headers. So I need to skip the first row and loop through the rest of this .CSV file.

Obviously throwing me some code would solve this, but more than that I would like an explanation of how to do this properly with PHP Data Objects (PDO). All the examples I've come across either have huge flaws or don't use PDO. Any and all help is welcomed and appreciated.

You only need a while. Throw away the first $data = fgetcsv. Then loop over the rest, and just run ->execute($data); — mario
– mario, Commented Aug 20, 2013 at 18:47
If your code is adding the first row, then you have the PDO side working fine; it's just the input part that needs looking at. All you need to do for that is put your fgetcsv in a while loop; add a flag you can set to see if this is the first line, and that should do it. — andrewsi
– andrewsi, Commented Aug 20, 2013 at 18:48
@YourCommonSense more or less. I would just like tips on proper practices when doing this. Obviously this is missing all file validation checking, but that's not what I need. I just wanted to make sure I was on the right track. From the comments so far it looks like I'm just missing the while loop, is this correct? — i_me_mine
– i_me_mine, Commented Aug 20, 2013 at 18:54
@mario thank you, so this is the proper way to do this then? I'm just missing my while loop? — i_me_mine
– i_me_mine, Commented Aug 20, 2013 at 18:55

i_me_mine · Accepted Answer · 2013-08-20 19:24:00Z

3

The comments helped me come up with this answer. I used the following code

if (isset($_FILES['uploadedfile'])) {   

    // get the csv file and open it up
    $file = $_FILES['uploadedfile']['tmp_name']; 
    $handle = fopen($file, "r"); 
    try { 
        // prepare for insertion
        $query_ip = $db->prepare('
            INSERT INTO projects (                                      
                id, project_name, contact, pm, apm, 
                est_start, est_end, trips, tasks, perc_complete, 
                bcwp, actual, cpi, bcws, bac, 
                comments, status, project_revenue, profit_margin, pm_perc, 
                audited
            ) VALUES (
                ?, ?, ?, ?, ?,
                ?, ?, ?, ?, ?, 
                ?, ?, ?, ?, ?,
                ?, ?, ?, ?, ?, 
                ?           
            )
        ');

        // unset the first line like this       
        fgets($handle);

        // created loop here
        while (($data = fgetcsv($handle, 1000, ',')) !== FALSE) {
            $query_ip->execute($data);
        }       

        fclose($handle);

    } catch(PDOException $e) {
        die($e->getMessage());
    }

    echo 'Projects imported';

} else {
    echo 'Could not import projects';
}

I hope this helps future readers properly import their .CSV files using PDO. It should be noted this should not be used on a live server/site. This code has 0 protection against potentially harmful uploads.

answered Aug 20, 2013 at 19:24

i_me_mine

1,4334 gold badges21 silver badges43 bronze badges

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

1 Comment

andrewsi Over a year ago

About the only suggestion I'd make is to look at the parameters for fgetcsv - if you specify a length parameter, it'll only read in that many characters at a time, so if a line goes over that length, it'll get split into two fields. You can set this to a number that's going be longer than the longest line (so 1000 is fine, if your data is short); or you can omit it (which would also mean omitting the separator, too)

William · Accepted Answer · 2015-06-04 12:34:56Z

error_reporting(E_ALL);
ini_set('display_errors', 1);

/* Database connections */
$_host = 'localhost';
$_name = 'root';
$_password = 'root';
$_database = 'TEST';

/* ========= Variables =========== */

/**
 * Defines the name of the table where data is to be inserted
 */
$table = 'terms';

/**
 * Defines the name of the csv file which contains the data
 */
$file = 'terms.csv';

/* =========== PDO ================= */
try {
$link = new PDO('mysql:dbname=' . $_database . ';host=' . $_host . ';charset=utf8', $_name, $_password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
    $link->exec("set names utf8");
} catch (PDOException $ex) {
    die(json_encode(array('outcome' => false, 'message' => 'Unable to connect')));
}
$link->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

/* ========= Load file content in an array ========== */
$rows = array_map('str_getcsv', file($file));
$header = array_shift($rows);
$csv = array();
foreach ($rows as $row) {
    $csv[] = array_combine($header, $row);
}

/* ========= Insert Script ========== */
foreach ($csv as $i => $row) {
    insert($row, $table);
}

function insert($row, $table) {
    global $link;
    $sqlStr = "INSERT INTO $table SET ";
    $data = array();
    foreach ($row as $key => $value) {
        $sqlStr .= $key . ' = :' . $key . ', ';
        $data[':' . $key] = $value;
    }
    $sql = rtrim($sqlStr, ', ');
    $query = $link->prepare($sql);
    $query->execute($data);
}

echo "Done inserting data in $table table";

Collectives™ on Stack Overflow

import .CSV file with PHP using PDO

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related