php - 使用 PHP 将 CSV 文件导入 MySQL

Question

我正在尝试使用 fgetcsv 函数将 CSV 数据导入 MySQL 数据库。

if(isset($_POST['submit'])) {
     $fname = $_FILES['sel_file']['name'];
     $var = 'Invalid File';
     $chk_ext = explode(".",$fname);

     if(strtolower($chk_ext[1]) == "csv") {
         $filename = $_FILES['sel_file']['tmp_name'];
         $handle = fopen($filename, "r");
     $res = mysql_query("SELECT * FROM vpireport");
     $rows = mysql_num_rows($res);
     if($rows>=0) {
        mysql_query("DELETE FROM vpireport") or die(mysql_error());

        for($i =1;($data = fgetcsv($handle, 10000, ",")) !== FALSE; $i++) {
            if($i==1)
            continue;
            $sql = "INSERT into vpireport
                                    (item_code,
                                     company_id,
                                     purchase,
                                     purchase_value) 
                                     values
                                    (".$data[0].",
                                     ".$data[1].",
                                     ".$data[2].",
                                     ".$data[3].")";
            //echo "$sql";
            mysql_query($sql) or die(mysql_error());
        }
    }

     fclose($handle);
    ?>
     <script language="javascript">
     alert("Successfully Imported!");
     </script>
     <?
 }

问题是它卡在导入过程中并显示以下错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'S',0,0)' at line 1

每次只导入部分文件。 10000 行文件中仅导入 200-300 行。

这是我的表的 DDL:

create table vpireport (
    id int not null auto_increment,
    item_code int,
    company_id int,
    purchase double,
    primary key(id),
    foreign key(company_id) references users(userid)
);

到目前为止我还没有找到问题所在，感谢任何帮助。谢谢。

Answer 1

您可能需要转义引号，这可以使用 PDO 来完成和 prepared statements .

为简洁起见，我跳过了示例中的大部分代码，只关注 for 循环。

<?php

    // Use PDO to connect to the DB
    $dsn = 'mysql:dbname=YOUR_DB;host=localhost';
    $user = 'DB_USERNAME';
    $password = 'DB_PASSWORD';

    try {
        $dbh = new PDO($dsn, $user, $password);
    } catch (PDOException $e) {
        echo 'Connection failed: ' . $e->getMessage();
    }

    for($i =1;($data = fgetcsv($handle, 10000, ",")) !== FALSE; $i++) {
        // The query uses placeholders for data
        $sql = "INSERT INTO vpireport
                    (item_code,company_id,purchase,purchase_value) 
                VALUES
                    (:item_code,:company_id,:purchase,:purchase_value)";
        $sth = $dbh->prepare($sql);

        // The data is bound to the placeholders
        $sth->bindParam(':item_code', $data[0]);
        $sth->bindParam(':company_id', $data[1]);
        $sth->bindParam(':purchase', $data[2]);
        $sth->bindParam(':purhcase_value', $data[3]);

        // The row is actually inserted here
        $sth->execute();
        $sth->closeCursor();
    }

不过，这不会消除任何有问题的字符，因此如果这会造成问题，您可能需要查看某种数据清理方法。