如何使用PHP将数据从CSV文件保存到数据库 [英] how to save data from csv file to database using php
问题描述
我想将csv文件保存到数据库中 这是我的代码:
I want to save my csv file into database Here is my code:
<title>Upload page</title>
<style type="text/css">
body {
background: #E3F4FC;
font: normal 14px/30px Helvetica, Arial, sans-serif;
color: #2b2b2b;
}
a {
color:#898989;
font-size:14px;
font-weight:bold;
text-decoration:none;
}
a:hover {
color:#CC0033;
}
h1 {
font: bold 14px Helvetica, Arial, sans-serif;
color: #CC0033;
}
h2 {
font: bold 14px Helvetica, Arial, sans-serif;
color: #898989;
}
#container {
background: #CCC;
margin: 100px auto;
width: 945px;
}
#form {padding: 20px 150px;}
#form input {margin-bottom: 20px;}
</style>
</head>
<body>
<div id="container">
<div id="form">
<?php
include "e2.php"; //Connect to Database
$deleterecords = "TRUNCATE TABLE books"; //empty the table of its current records
mysql_query($deleterecords);
//Upload File
if (isset($_POST['submit'])) {
if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "</h1>";
echo "<h2>Displaying contents:</h2>";
readfile($_FILES['filename']['tmp_name']);
}
//Import uploaded file to Database
$handle = fopen($_FILES['filename']['tmp_name'], "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$import="INSERT INTO books (BookID,Title,Author,PublisherName,CopyrightYear) VALUES('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";
mysql_query($import) or die(mysql_error());
}
fclose($handle);
print "Import done";
//view upload form
}else {
print "Upload new csv by browsing to file and clicking on Upload<br />\n";
print "<form enctype='multipart/form-data' action='index.php' method='post'>";
print "File name to import:<br />\n";
print "<input size='50' type='file' name='filename'><br />\n";
print "<input type='submit' name='submit' value='Upload'></form>";
}
?>
</div>
</div>
</body>
</html>
这是e2.php文件:
Here is e2.php file:
$db = mysql_connect("localhost","root","") or die("Could not connect.");
if(!$db)
die("no db");
if(!mysql_select_db("books",$db))
die("No database selected.");
问题是如何同时在两个表中保存数据，在csv文件中，每个单元都有链接，例如:A1是到sheet2的超链接，我也想保存工作表2的数据，就像A1是主键一样.
Problem is how to save data in two table simultaneously, in csv file there is link given to every cell, eg: A1 is hyperlink to sheet2, i want to save sheet 2 data also like A1 is primary key.
这是book.csv sheet1，要保存在"books"表中.
here is book.csv sheet1 to save in "books" table.
1 Geography Namrata Harshal 01-04-14
2 Geography Namrata Harshal 02-04-14
3 Geography Namrata Harshal 03-04-14
4 Geography Namrata Harshal 04-04-14
5 Geography Namrata Harshal 05-04-14
6 Hindi Namrata Harshal 06-04-14
7 Hindi Namrata Harshal 07-04-14
8 Hindi Namrata Harshal 08-04-14
9 Hindi Namrata Harshal 09-04-14
10 Hindi Namrata Harshal 10-04-14
这是book.csv sheet2，要保存在详细信息"表中.
here is book.csv sheet2 to save in "details" table.
Geography Namrata Harshal 02-04-14
Geography Namrata Harshal 03-04-14
Geography Namrata Harshal 04-04-14
Geography Namrata Harshal 05-04-14
推荐答案
使用fgetcsv函数从CSV文件获取数据.
Get Data from CSV File use fgetcsv function.
$row = 1;
if (($openfile = fopen("customer.csv", "r")) !== FALSE) {
while ($getdata = fgetcsv($openfile, 1000, ",")) {
$total = count($getdata);
echo "<b>Row no:-</b>$row\n";
echo "<b>Total fields in this row:-</b>$total\n";
$row++;
for ($c=0; $c < $total; $c++) {
$csvdata = implode(";", $getdata);
$fncsvdata = explode(";", $csvdata);
}
var_dump($fncsvdata);
}
}
在这里，您可以看到比要使用INSERT查询插入数据要多的CSV文件数据.
对于插入第一列，请使用$fncsvdata[0].
在fgetcsv中，这里1000 =必须大于CSV文件中可以找到的最长行(以字符为单位).
Here You can see your CSV file data than you want to use INSERT query for insert data.
For insert frist colum use $fncsvdata[0].
Here in fgetcsv 1000 = "Must be greater than the longest line (in characters) to be found in the CSV file".
这篇关于如何使用PHP将数据从CSV文件保存到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
