如何在mysql中导入Excel文件？

I have a code in which I can import .CSV file in my mysql. But I can not import Excel file. Some garbage values insert in my database while I import Excel file.

我有一个代码,我可以在我的mysql中导入.CSV文件。但是我无法导入Excel文件。导入Excel文件时,我的数据库中插入了一些垃圾值。

<?php 
if(isset($_POST["Import"]))
{
//First we need to make a connection with the database
include("connection.php");
echo $filename=$_FILES["file"]["tmp_name"];
if($_FILES["file"]["size"] > 0)
{
$file = fopen($filename, "r");
//$sql_data = "SELECT * FROM prod_list_1 ";
while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE)
{
//print_r($emapData);
//exit();
$sql = "INSERT into city(city_id,city_name) values ('$emapData[0]','$emapData[1]')";
mysql_query($sql);
}
fclose($file);
echo 'CSV File has been successfully Imported';
header('Location: index.php');
}
else
echo 'Invalid File:Please Upload CSV File';
}
?>
<form enctype="multipart/form-data" method="post" role="form">
<div class="form-group">
<label for="exampleInputFile">File Upload</label>
<input type="file" name="file" id="file" size="150">
<p class="help-block">Only Excel/CSV File Import.</p>
</div>
<button type="submit" class="btn btn-default" name="Import" value="Import">Upload</button>
</form>

2 个解决方案

#1

Looks like you are trying to read an excel file using some code that is able to parse only csv files, you can't, the format is completely different.

看起来你正在尝试使用一些只能解析csv文件的代码来读取excel文件,你不能,格式完全不同。

Check for filetype and use a library like PHPExcel if the user sends you an excel spreadsheet.

如果用户向您发送excel电子表格,请检查文件类型并使用像PHPExcel这样的库。

#2

You can use EasyXLS Excel library to import Excel file and than to insert data into MySQL database:

您可以使用EasyXLS Excel库导入Excel文件,而不是将数据插入MySQL数据库:

$workbook = new COM("EasyXLS.ExcelDocument");
$rows = $workbook->easy_ReadXLSActiveSheet_AsList($filename);

For more details see this link about importing Excel file to MySQL using EasyXLS.

有关更多详细信息,请参阅此链接,了解如何使用EasyXLS将Excel文件导入MySQL。

#1