import txt/csv data into database according to database table header

Question

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";

    }

Don't use `mysql_*` as it's going to be deprecated, switch to `PDO` or `mysqli_*`. Also [here's](http://stackoverflow.com/questions/10478861/importing-multiple-csv-files-to-mysql-tables) an example of `LOAD LOCAL INFILE` using PDO that might be useful — Elias Van Ootegem, Sep 04 '12 at 11:41

score 0 · Answer 1 · answered Sep 04 '12 at 11:28

You can use MySQL's LOAD DATA INFILE command. As documented in the manual:

You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:
LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3);

Therefore, in your case:

LOAD DATA INFILE '/path/to/file' INTO TABLE month
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  IGNORE 1 LINES
  (first_name, last_name, @dummy);

score 0 · Accepted Answer · answered Sep 04 '12 at 11:29

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)

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

Check this link.

import txt/csv data into database according to database table header

2 Answers2

Linked