如何使用php将数据从excel导出到mysql?
问题描述:
我试过代码是如何使用php将数据从excel导出到mysql?
$source = fopen('filename.csv', 'r') or die("Problem open file");
while (($data = fgetcsv($source, 1000, ",")) !== FALSE)
{
$name = $data[0];
`$mobile = $data[1];
mysql_query("INSERT INTO `table` (`name`,`mobile`) VALUES ('".$name."','".$mobile ."') ");
}
fclose($source);
,但只需要最后一个记录......
答
下面是一个简单的方法来做到这一点,使用PHP fgetcsv()
和mysqli()
:
1.连接到库MySQLi:
$mysqli = new mysqli("localhost", "my_user", "my_password", "my_db");
2.打开CSV文件:
$source = fopen('filename.csv', 'r') or die("Problem open file");
3.循环读取CSV,把你的插入查询的变量:
$query="";
do {
if ($fields[0]) {
$query.="INSERT INTO `table_name` (`name`, `mobile`) VALUES
(
'".addslashes($fields[0])."',
'".addslashes($fields[1])."'
)
;";
}
} while ($fields = fgetcsv($source,1000,",","'"));
4. EXCUTE查询并关闭mysqli:
if($mysqli->multi_query($query)) echo "Data successfuly added";
$mysqli->close();
答
为什么不直接添加使用MySQL整个文件,而不是通过线打破它呢?喜欢的东西
LOAD DATA INFILE 'filename.csv' INTO TABLE table FIELDS TERMINATED BY ',';
,或者如果你有需要进入特定领域的特定列,
LOAD DATA INFILE 'filename.csv' INTO TABLE table FIELDS TERMINATED BY ',' (@c1, @c2)
set name = @c1, mobile = @c2;