我有以下代码用于将csv文件上载到mysql数据库。它的工作原理很好,但是如果csv文件为每一列包含一个标题,那么它将被上传到表的第一行。我想删除csv文件的第一行,同时将其存储在数据库中。我该怎么做?

<?php

    //connect to the database
    $connect = mysql_connect("localhost","root","");
    mysql_select_db("crm",$connect); //select the table
    //

    if ($_FILES[csv][size] > 0) {

        //get the csv file
        $file = $_FILES[csv][tmp_name];
        $handle = fopen($file,"r");

        //loop through the csv file and insert into database
        do {
            if ($data[0]) {
                mysql_query("INSERT INTO lead (name, lead_value, status) VALUES
                    (
                        '".addslashes($data[0])."',
                        '".addslashes($data[1])."',
                        '".addslashes($data[2])."'
                    )
                ");
            }
        } while ($data = fgetcsv($handle,1000,",","'"));
        //

        //redirect
        header('Location: import.php?success=1'); die;

    }

    ?>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>Import a CSV File with PHP & MySQL</title>
    </head>

    <body>

    <?php if (!empty($_GET[success])) { echo "<b>Your file has been imported.</b><br><br>"; } //generic success notice ?>

    <form action="" method="post" enctype="multipart/form-data" name="form1" id="form1">
      Choose your file: <br />
      <input name="csv" type="file" id="csv" />
      <input type="submit" name="Submit" value="Submit" />
    </form>

    </body>
    </html>

最佳答案

我正在使用的示例代码,它应该可以工作,因为我正在生产环境中使用它

$handle = fopen("stuff.csv","r");

/* assuming that first row has collumns names */
$query = "INSERT INTO table(";
$cols = fgetcsv($handle,100000,';','"');
$query .= implode(", ",$cols).") VALUES";

$values = "";

    /* cycle through each row and build mysql insert query */
while($data = fgetcsv($handle,100000,';','"')) {
    $values .= " ( ";

    foreach($data as $text) {

        $values .= "'".$text."', ";

    }

    $values = substr($values,0,-2);

    $values .= "), ";

}

    /* remove last 2 chars */

$values = substr($values,0,-2);

$query .= $values;

echo $query;

fclose($handle);

请注意,这个脚本将返回mysql查询。。。不执行它,所以根据你的需要修改它。

关于php - 删除CSV文件中的第一行上传到MySQL数据库,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/8142048/

10-13 09:29