使用php代码在mysql中导入csv文件

mwg9r5ms 于 2021-06-24 发布在 Mysql

关注(0)|答案(4)|浏览(391)

我必须使用php代码导入mysql数据库表中的csv文件。csv文件如下：
"2016-09-02", "100.01", "4005.09", "5000", "1.09", "120.09", "100.5", "200.77" "2016-09-03", "150.01", "4205.09", "5600", "1.10", "150.09", "300.5", "300.77"
文件权限为755。
表字段是9（包括id字段）：第一个是datetime字段，其他是float字段。我使用的代码如下，它将在服务器站点上运行：

$csvFile = "../scripts/tabella.csv";

$db = @mysql_connect('**.***.**.***', 'Sql******', '*******');
@mysql_select_db('Sql******_*');

$query = 'LOAD DATA LOCAL INFILE \' '. $csvFile .' \' INTO TABLE mytable FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\' LINES TERMINATED BY \'\n\' ';

if(!mysql_query($query)){
    die(mysql_error());
}
mysql_close($db);

第一个错误是从mysql返回的'u error:'file not found'。csv文件在'www.mysite.it/mysite/scripts/tabella.csv'. 它的权限是755。我尝试使用realpath（$csvfile）函数，但错误总是一样的。
我尝试在localhost中运行同一个查询，但没有出现此错误，只在表中插入了一条记录，其fild值为null。
你能帮帮我吗？谢谢！

mysql csv php Import

来源：https://stackoverflow.com/questions/39868675/import-csv-file-in-mysql-using-php-code

4条答案

按热度按时间

nwo49xxi1#

尝试这个简单的方法将csv数据导入mysql数据库 file 是要删除的html元素名称 browse 您的文件路径。

if(isset($_POST["submit"]))
{
    $file = $_FILES['file']['tmp_name'];
    $handle = fopen($file, "r");
    $c = 0;
    while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
    {
        $name= $filesop[0];

        $sql = mysql_query("INSERT INTO tab(name) VALUES ('$name')");
        $c = $c + 1;
    }

    if($sql){
        echo "You database has imported successfully. You have inserted ". $c ." recoreds";
    }else{
        echo "Sorry! There is some problem.";
    }
}

赞(0）回复(0）举报 2021-06-24

q8l4jmvw2#

使用mysql\u error获取错误

if(!mysql_query($query)){
      die(mysql_error());

 }

赞(0）回复(0）举报 2021-06-24

tp5buhyn3#

尝试以下代码。。

$csvFile = "../scripts/tabella.csv";

//The Connection
$mysqli = new mysqli($host,$username,$password,$database);

//Check for successful connection
if ($mysqli->connect_errno) echo "Error - Failed to connect to MySQL: " . $mysqli->connect_error; die;

$query = 'LOAD DATA LOCAL INFILE \' '. $csvFile .' \' INTO TABLE mytable FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\' LINES TERMINATED BY \'\n\' ';
//Do your query
$result = mysqli_query($mysqli,$query);

//Close the connection
mysqli_close($mysqli);

赞(0）回复(0）举报 2021-06-24

h7appiyu4#

试试这个编码，很管用

<?php 
    $connect = mysqli_connect("localhost", "root", "", "testing");
    if(isset($_POST["submit"]))
{
    if($_FILES['file']['name'])
    {
        $filename = explode(".", $_FILES['file']['name']);
        if($filename[1] == 'csv')
        {
            $handle = fopen($_FILES['file']['tmp_name'], "r");
            while($data = fgetcsv($handle))
            {
                $item1 = mysqli_real_escape_string($connect, $data[0]);  
                $item2 = mysqli_real_escape_string($connect, $data[1]);
                $query = "INSERT into excel(excel_name, excel_phone) values('$item1','$item2')";
                mysqli_query($connect, $query);
            }
            fclose($handle);
            echo "<script>alert('Import done');</script>";
        }
    }
}
?>
<!DOCTYPE html>
<html>
<head>
    <title></title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>  
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
</head>
<body>
    <form method="post" enctype="multipart/form-data">
        <div align="center">
            <label>Select CSV File:</label>
            <input type="file" name="file" />
            <br />
            <input type="submit" name="submit" value="Import" class="btn btn-info" />
        </div>
    </form>
</body>
</html>

赞(0）回复(0）举报 2021-06-24

我来回答

使用php代码在mysql中导入csv文件

4条答案

相关问题

热门标签

最新问答