Cannot import CSV file to SQL (using PDO)

Question

I have a .csv file named 'result.csv'. it contains:

1,2,3,4,6,5,7

I want to import the .csv file to my_table in SQL (I want to put the .csv value to 'id_2' column) with this code, but this code doesn't work properly.

<?php
    $host = "localhost";
    $db_name = "tsp";
    $username = "root";
    $password = "";
    $conn = new PDO("mysql:host=" .$host . ";dbname=" . $db_name, $username, $password);

define('CSV_PATH','C:/Users/user/Downloads/'); // CSV file path

$csv_file = CSV_PATH . "result.csv"; // Name of CSV file
$csvfile = fopen($csv_file, 'r');
$theData = fgets($csvfile);
$i = 0;
while (!feof($csvfile))
{
   $csv_data[] = fgets($csvfile, 1024);
   $csv_array = explode(",", $csv_data[$i]);
   $insert_csv = array();
   $insert_csv['id_2'] = $csv_array[0];

   $sql = $conn->prepare("INSERT INTO my_table(id_2) VALUES('','".$insert_csv['id_2'].")");
   $sql->execute();

   $i++;
}

fclose($csvfile);
?>

What should I do? Thanks for your help

score 0 · Answer 1 · answered Jun 25 '19 at 07:57

It's a late answer, but I hope I can help. Consider the following:

use file() to read file content
your INSERT statement is not correct - you have only one column in your columns list, but two values ("INSERT INTO my_table(id_2) VALUES('','".$insert_csv['id_2'].")").
use prepared statement to execute your INSERT statement.

CSV file (result.csv):

1,2,3,4,6,5,7
11,12,13,14,16,15,17

PHP:

<?php
// Connection
$host     = "localhost";
$db_name  = "tsp";
$username = "root";
$password = "";
try {
    $conn = new PDO("mysql:host=" .$host . ";dbname=" . $db_name, $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Error connecting to SQL Server".$e->getMessage());
}

// Read file
define('CSV_PATH', 'C:/Users/user/Downloads/');
$filename = CSV_PATH."result.csv";
$file     = file($filename, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);

// Read lines and insert data
try {
    $sql = $conn->prepare("INSERT INTO my_table(id_2) VALUES(?)");
    foreach ($file as $row) {
        $ids = explode(',', $row);
        foreach($ids as $id) {
            $sql->bindValue(1, $id);
            $sql->execute();
            echo $id."<br>";
        }
    }
} catch(PDOException $e) {
    die("Error executing query" );
}

?>

Cannot import CSV file to SQL (using PDO)

1 Answers1