根据数据库表头将txt / csv数据导入数据库 [英] import txt/csv data into database according to database table header

问题描述

我的tex / csv文件有3列 first_name ， last_name 和 code>，但我的数据库表有两列 first_name 和 last_name 。我希望有一个函数，即使在 csv 中有许多列不存在于表中，它仍然可以选择正确的列和插入到表中。
有人建议我使用php mysql_fetch_field 。我尝试 mysql_fetch_field ，我可以收集我的数据库表列名称，但我不能根据表头插入数据。

My tex/csv file has 3 columns first_name, last_name and address but my database table has two columns first_name and last_name. I wish to have a function that even there are many columns in csv that not exist in the table it still can just pick the right column and insert into table. Some one advise me using php mysql_fetch_field. I am trying mysql_fetch_field and I can collect my database table columns name but I can't insert data according to table header.

     set_time_limit(0);

    include "connection.php"; //Connect to Database


    if (isset($_POST['submit'])) {

    // Gel table current column name

    $query = "select * from month";
    $result=mysql_query($query) or die (mysql_error());

    $i = 0;
    while ($i < mysql_num_fields($result)) {
        $meta = mysql_fetch_field($result, $i);

        $a=$meta->name;

        $i++;
    }

    //Upload File

    if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
            echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "</h1>";
            echo "<h2>Displaying contents:</h2>";
            readfile($_FILES['filename']['tmp_name']);
        }

        //Import uploaded file to Database
        $handle = fopen($_FILES['filename']['tmp_name'], "r");

            $header = fgetcsv($handle);


        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            $import="INSERT into month($header[0],$header[1]) values('$data[0]','$data[1]')";

            mysql_query($import) or die(mysql_error());
        }

        fclose($handle);

        echo "Import done";

    }

推荐答案

不确定我得到你需要什么，但我想你应该使用 MySQL Load Data Infile 加载 CSV ：

I'm not sure I get what you need but I think you should use MySQL Load Data Infile to load that CSV like this:

LOAD DATA LOCAL INFILE 'your_file.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\r\n'
(field1, field2, field3)

b $ b

这是我使用 CSV 的方式，它的工作非常好。

That's how I work with CSV and it does the job pretty well.

链接。

这篇关于根据数据库表头将txt / csv数据导入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！