如何使用PHP将Excel或CSV上传到MySQL数据库？ [英] How to upload Excel or CSV to MySQL data base using PHP?

问题描述

我有一个包含数据（电话号码列表）的Excel文件，并且我希望使用PHP代码将Excel文件导入到我的数据库表（例如phonenumber_list）中？我知道如何将MySQl转换为Excel，但是可以相反吗？请帮助！

解决方案

CSV

如果可以转换Excel文件到CSV，您可以使用 mysqlimport 导入CSV。这可能是将数据导入MySQL的最快方法。

您可以使用 LOAD DATA INFILE 。这是一个示例SQL语句导入 data.csv ：

  LOAD数据INFILE'data.csv'INTO TABLE phonenumber_list 
 FIELDS TERMINATED BY'，'ENCLOSED BY'''
 LINES TERMINATED BY'\r\\\
'
 IGNORE 1 LINES; 
  

Excel

如果您不能使用CSV，要使用原始Excel文件，您将需要一个能够读取Excel文件的PHP库。

有几个可用，但我不知道如何可靠或者维护得很好：

Pear：Spreadsheet_Excel_Writer

PHPExcel

PHP-ExcelReader

您可能还想看看使用Excel API的另一种方法，但您需要安装Excel才能执行此操作。有一些关于这方面的信息：

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

如果你使用这种方法，你需要写一些读取和解析Excel文件的代码，并逐行发送到MySQL。这可能比批量CSV导入慢得多。

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!

解决方案

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.

这篇关于如何使用PHP将Excel或CSV上传到MySQL数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！