用于将csv数据导入mysql的PHP脚本
用于将csv数据导入mysql的PHP脚本
提问于 2014-01-02 11:48:15
我已经尝试了下面的代码，但得到了一些错误。这里我可以读取输入文件，但是我得到了以下错误: deprecated :函数split()在C:\wamp\www\aaj2\index.php的第63行被弃用。O/P:在此csv文件中共找到5124条记录。
<?php
$databasehost = "localhost";
$databasename = "test";
$databasetable = "sample";
$databaseusername="test";
$databasepassword = "";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "filename.csv";
$addauto = 0;
$save = 1;
$outputfile = "output.sql";
if(!file_exists($csvfile)) { echo "File not found. Make sure you specified the correct path.\n"; exit; }
$file = fopen($csvfile,"r");
if(!$file) { echo "Error opening data file.\n"; exit; }
$size = filesize($csvfile);
if(!$size) { echo "File is empty.\n"; exit; }
$csvcontent = fread($file,$size);
fclose($file);
$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error()); @mysql_select_db($databasename) or die(mysql_error());
$lines = 0; $queries = ""; $linearray = array();
foreach(split($lineseparator,$csvcontent) as $line) {
$lines++;
$line = trim($line," \t"); $line = str_replace("\r","",$line); /************************************ This line escapes the special character. remove it if entries are already escaped in the csv file ************************************/ $line = str_replace("'","\'",$line); /*************************************/ $linearray = explode($fieldseparator,$line); $linemysql = implode("','",$linearray); if($addauto) $query = "insert into $databasetable values('','$linemysql');"; else $query = "insert into $databasetable values('$linemysql');"; $queries .= $query . "\n";
@mysql_query($query); }
@mysql_close($con);
if($save) { if(!is_writable($outputfile)) { echo "File is not writable, check permissions.\n"; } else { $file2 = fopen($outputfile,"w");
if(!$file2) { echo "Error writing to the output file.\n"; } else { fwrite($file2,$queries); fclose($file2); } } }
echo "Found a total of $lines records in this csv file.\n";
?>编辑:错误:文件不可写，请检查权限。在此csv文件中共找到5124条记录。
回答 3
Stack Overflow用户
发布于 2014-01-02 13:13:30
试试这个：
<?php
// specify connection info
$connect = mysql_connect('localhost','root','12345');
if (!$connect)
{
die('Could not <span id="IL_AD1" class="IL_AD">
connect to</span> MySQL: ' . mysql_error());
}
$cid =mysql_select_db('test',$connect); //specify db name
define('CSV_PATH','C:/wamp/www/csvfile/'); // specify CSV file path
$csv_file = CSV_PATH . "infotuts.csv"; // Name of your CSV file
$csvfile = fopen($csv_file, 'r');
$theData = fgets($csvfile);
$i = 0;
while (!feof($csvfile))
{
$csv_data[] = fgets($csvfile, 1024);
$csv_array = explode(",", $csv_data[$i]);
$insert_csv = array();
$insert_csv['ID'] = $csv_array[0];
$insert_csv['name'] = $csv_array[1];
$insert_csv['email'] = $csv_array[2];
$query = "INSERT INTO csvdata(ID,name,email)
VALUES('','".$insert_csv['name']."','".$insert_csv['email']."')";
$n=mysql_query($query, $connect );
$i++;
}
fclose($csvfile);
echo "File data successfully imported to database!!";
mysql_close($connect); // closing connection
?>Stack Overflow用户
发布于 2014-05-15 16:46:44
我认为您需要先转换集合中的CSV文件。之后，您可以添加循环并调用insert查询来插入数据。
在这里，您将获得将CSV文件转换为集合数组的代码。best-way-to-upload-and-read-csv-file-in-php
Stack Overflow用户
发布于 2015-10-01 15:06:26
**Data Import And Export**
**CSV To Mysql AND Mysql To CSV Using Mysqli**
<!DOCTYPE html>
<!--
To change this license header, choose License Headers in Project Properties.
To change this template file, choose Tools | Templates
and open the template in the editor.
-->
<html>
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body>
<form method="post">
<button type="submit" name="btn_export">Data Export</button>
<button type="submit" name="btn_import">Data Import</button>
</form>
<?php
$host = "localhost";
$uname = "root";
$pass = "";
$database = "demo"; //Change Your Database Name
$conn = new mysqli($host, $uname, $pass, $database)or die("No Connection");
echo mysql_error();
//MYSQL MYADDMINPHP TO CSV
if (isset($_REQUEST['btn_export']))
{
$data_op = "";
$sql = $conn->query("select * from users"); //Change Your Table Name
while ($row1 = $sql->fetch_field())
{
$data_op .= '"' . $row1->name . '",';
}
$data_op .="\n";
while ($row = $sql->fetch_assoc())
{
foreach ($row as $key => $value)
{
$data_op .='"' . $value . '",';
}
$data_op .="\n";
}
$filename = "Database.csv"; //Change File type CSV/TXT etc
header('Content-type: application/csv'); //Change File type CSV/TXT etc
header('Content-Disposition: attachment; filename=' . $filename);
echo $data_op;
exit;
}
//CSV To MYSQL MYADDMINPHP
if (isset($_REQUEST['btn_import']))
{
$filename = 'Database.csv';
$fp = fopen($filename, "r");
while (($row = fgetcsv($fp, "40", ",")) != FALSE)
{
$sql = "INSERT INTO users (name,pass,city,id) VALUES('" . implode("','", $row) . "')";
if (!$conn->query($sql))
{
echo '<br>Data No Insert<br>';
}
}
fclose($fp);
}
?>
</body>
</html>页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/20876043
复制相关文章
相似问题