通过php将csv文件导入mysql - Import csv file into mysql via php

Import csv file into mysql via php

本文关键字：导入 mysql 文件 csv php 通过 | 更新日期: 2023-09-27

我正在使用php将csv文件导入我的数据库（Mysql）。下面的代码只将第一行（csv文件中的数据）插入数据库。但它应该向数据库中插入不止一行。

$info = pathinfo($_FILES['file']['name']);
/**
 * This checks if the file is a csv file
 */
if(strtolower($info['extension']) == 'csv'){
    $filename=$_FILES["file"]["tmp_name"];
    if($_FILES["file"]["size"] > 0){
        //Open the file
        $file = fopen($filename, "r");
        while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE){
            $num = count($emapData);
            /**
             * Insert data into the database if the columns are 
             *  exactly in three columns
             */ 
            if ($num == 3){
                $sql = "INSERT 
                        INTO Users (firstName,surName,indexNo)
                    VALUES('$emapData[0]','$emapData[1]','$emapData[2]')";
                $result=$db->query($sql);

                if($result){
                    echo "<script type='"text/javascript'">
                            alert('"CSV File has been successfully Imported.'");
                            window.location = '"../administrator/bulkStudentReg.php'"
                        </script>";
                }
                else{
                    echo "<script type='"text/javascript'">
                    alert('"Please Upload CSV File was not successful.'");
                    window.location = '"../administrator/bulkStudentReg.php'"
                    </script>";
                }
            }
            else{
                echo "<script type='"text/javascript'">
                    alert('"UPLOAD FAILED: Please your csv file contains incomplete column/s. Column should be 3 columns'");
                    window.location = '"../administrator/bulkStudentReg.php'"
                    </script>";
            }
        fclose($file);
        }
    }
}
else{
    echo "<script type='"text/javascript'">
                    alert('"UPLOAD FAILED: Please the file should be in a csv file, eg. students.csv '");
                    window.location = '"../administrator/bulkStudentReg.php'"
                    </script>";
}

您可以尝试使用MySQL内置函数来插入CSV数据，如果您的数据结构与表结构相同，则非常简单：

$sql = "LOAD DATA INFILE '$filename' INTO TABLE Users
FIELDS TERMINATED BY ','"

查看MySQL LOAD DATA文档了解更多详细信息

你在找这样的东西吗。

我没有测试，因为我没有你的样本数据，但请试一试。

<?php
$info = pathinfo($_FILES['file']['name']);
/**
 * This checks if the file is a csv file
 */
if(strtolower($info['extension']) == 'csv'){
    $filename=$_FILES["file"]["tmp_name"];
    if($_FILES["file"]["size"] > 0){
        //Open the file
        $file = fopen($filename, "r");
        while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE){
            $num = count($emapData);
            /**
             * Insert data into the database if the columns are 
             *  exactly in three columns
             */ 
            if ($num == 3){
                $sql = "INSERT 
                        INTO Users (firstName,surName,indexNo)
                    VALUES('$emapData[0]','$emapData[1]','$emapData[2]')";
                $result=$db->query($sql);
            }
        }
        if($result){
                    echo "<script type='"text/javascript'">
                            alert('"CSV File has been successfully Imported.'");
                            window.location = '"../administrator/bulkStudentReg.php'"
                        </script>";
                }else{
                    echo "<script type='"text/javascript'">
                    alert('"Please Upload CSV File was not successful.'");
                    window.location = '"../administrator/bulkStudentReg.php'"
                    </script>";
                }
    }
    fclose($file);
}
else{
    echo "<script type='"text/javascript'">
                    alert('"UPLOAD FAILED: Please the file should be in a csv file, eg. students.csv '");
                    window.location = '"../administrator/bulkStudentReg.php'"
                    </script>";
}
?>