How to upload Excel or CSV to MySQL data base using PHP?

Question

I have an Excel file containing data(list of Phone numbers), and i want that data from, Excel file to be imported in to my Database table(for example called phonenumber_list) using PHP code? i know how to convert MySQl in to Excel, but can it possible in reverse? Please Help!

Answer 1

CSV
If you can convert the Excel file to CSV first, you can use mysqlimport to import CSV. This is probably the quickest method for getting the data into MySQL.

You can do this from PHP using LOAD DATA INFILE. This is a sample SQL statement to import data.csv:

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

Excel
If you cannot use CSV, and need to work with raw Excel files, you will need a PHP library which is capable of reading Excel files.

There are a few available, but I don't know how reliable or how well maintained they are:

Pear: Spreadsheet_Excel_Writer

PHPExcel

PHP-ExcelReader

You may also want to look at the alternative approach of using the Excel API, but you'll need Excel installed to do that. There's a little information about that here:

http://www.sydphp.org/presentations/010606-excel.html

If you use this approach, you will need to write some code that reads and parses the Excel file, and sends it to MySQL row-by-row. This may work out a lot slower than a bulk CSV import.