Site icon Shaharia's Blog

Insert data from CSV file in PHP with PDO

developer working on laptop.jpg

developer working on laptop.jpg

CSV is a very important file types to save mass amount of similar types data. Sometime to save those data from CSV file in PHP is an important task. So I am sharing a complete script to read data from CSV and save it in your data with PHP & PDO.

<?php

// Database Connection
$hostname = "localhost";
$username = "root";
$password = "mf2312";

try {
    $db = new PDO("mysql:host=$servername;dbname=csvtest", $username, $password);
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}




// Create  CSV to Array function

function csvToArray($filename = '', $delimiter = ',')
{
    if (!file_exists($filename) || !is_readable($filename)) {
        return false;
    }

    $header = NULL;
    $result = array();
    if (($handle = fopen($filename, 'r')) !== FALSE) {
        while (($row = fgetcsv($handle, 1000, $delimiter)) !== FALSE) {
            if (!$header)
                $header = $row;
            else

                $result[] = array_combine($header, $row);
        }
        fclose($handle);
    }


    return $result;
}




// Insert data into database   

    $all_data = csvToArray('files/testcsv.csv');
    foreach ($all_data as $data) {

        $sql = $db->prepare("INSERT INTO students (name, roll, department) 
        VALUES (:name, :roll, :department)");
        $sql->bindParam(':name', $data['name']);
        $sql->bindParam(':roll', $data['roll']);
        $sql->bindParam(':department', $data['department']);
        $sql->execute();

    }

Write your comments if this works for you.