How can I load an .xls file to a mysql database in php?

Question

I want the exact opposite of this.

I want to load a .xls file full of emails into a database column.

What's the simplest way to do this?

Answer 1

1. Export the Excel spreadsheet to CSV format

2. Use MySQL's LOAD DATA INFILE syntax to use the CSV file created in Step 1, using:

   LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
     FIELDS TERMINATED BY ',' ENCLOSED BY '"'
     LINES TERMINATED BY '\r\n'
     IGNORE 1 LINES;
   

The file will have to be locally accessible to the MySQL instance, so you'll have to upload it if the server isn't physically accessible.

Answer 2

You can use PHPExcel, which can read most Excel file formats (BIFF5, BIFF8, spreadsheetML). Unfortunately the project's docs are in a word file, so can't link directly to the specific section, but it's section 6.4.1 / page 37 of this file.

Answer 3

Like Ponies said, you will have to first "Save As > CSV" then upload and do whatever. Often permissions will not allow that SQL statement however. My personal favorite is using csv2sql.com because it will do the heavy lifting and give you the SQL to just execute, however you could use fgetcsv which would be more secure, and something you can implement into a script.

With fgetcsv, you could do something like this assuming you just had a one column file with the email addresses listed:

$row = 1;
if (($handle = fopen("test.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        //echo "<p> $num fields in line $row: <br /></p>\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            if ($c==0){
                 //This is the first column
                 //Just run a generic MySQL insert command here (see below for link for more info on this)
            }
        }
    }
    fclose($handle);
}

Tutorial on PHP MySQL Insert: at W3Schools. That should get you going on this endeavor.