php 如何使用 codeigniter 将 CSV 导入 mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15925997/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I import CSV to mysql using codeigniter
提问by Fahid Mohammad
I have a list of csv files, which I want to be import to mysql db, using codeigniter.
我有一个 csv 文件列表，我想使用 codeigniter 将其导入 mysql db。
I tried $this->load->library('getcsv');library but how to import the csv without uploading the CSV files to the server, what I meant was just uploading the files to a temp location like how pure coding PHP does.
我尝试了$this->load->library('getcsv');库，但如何在不将 CSV 文件上传到服务器的情况下导入 csv，我的意思只是将文件上传到一个临时位置，就像纯编码 PHP 所做的那样。
Any idea?
任何的想法？
Example:
例子：
I wanted something like this.
我想要这样的东西。
HTML:<input type="file" name="csv" />
HTML：<input type="file" name="csv" />
to a temp string
到临时字符串
Codeigniter:$this->input->upload['tmp_name']
代码点火器：$this->input->upload['tmp_name']
采纳答案by Muhammad Raheel
I gave exactly the answer for this. Take a look at this answer of mine for your problem's solution.
对此，我给出了准确的答案。看看我的这个答案，看看你的问题的解决方案。
This will generate report in CSV format.
这将生成 CSV 格式的报告。
And this will tell you how to read and dump csv files.
这将告诉您如何读取和转储 csv 文件。
回答by Priya jain
Try This:
尝试这个：
Database:
数据库：
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `contacts`;
CREATE TABLE `contacts` (
`contact_id` int(11) NOT NULL auto_increment,
`contact_first` varchar(255) character set latin1 default NULL,
`contact_last` varchar(255) character set latin1 default NULL,
`contact_email` varchar(255) character set latin1 default NULL,
PRIMARY KEY (`contact_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
import.php::
导入.php::
<?php
//connect to the database
$connect = mysql_connect("localhost","username","password");
mysql_select_db("mydatabase",$connect); //select the table
//
if ($_FILES[csv][size] > 0) {
//get the csv file
$file = $_FILES[csv][tmp_name];
$handle = fopen($file,"r");
//loop through the csv file and insert into database
do {
if ($data[0]) {
mysql_query("INSERT INTO contacts (contact_first, contact_last, contact_email) VALUES
(
'".addslashes($data[0])."',
'".addslashes($data[1])."',
'".addslashes($data[2])."'
)
");
}
} while ($data = fgetcsv($handle,1000,",","'"));
//
//redirect
header('Location: import.php?success=1'); die;
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Import a CSV File with PHP & MySQL</title>
</head>
<body>
<?php if (!empty($_GET[success])) { echo "<b>Your file has been imported.</b><br><br>"; } //generic success notice ?>
<form action="" method="post" enctype="multipart/form-data" name="form1" id="form1">
Choose your file: <br />
<input name="csv" type="file" id="csv" />
<input type="submit" name="Submit" value="Submit" />
</form>
</body>
</html>
//get the csv file
$file = $_FILES[csv][tmp_name];
$handle = fopen($file,"r");
//loop through the csv file and insert into database
do {
if ($data[0]) {
mysql_query("INSERT INTO contacts_tmp (contact_first, contact_last, contact_email) VALUES
(
'".addslashes($data[0])."',
'".addslashes($data[1])."',
'".addslashes($data[2])."'
)
");
}
} while ($data = fgetcsv($handle,1000,",","'"));
//
回答by Suraj Kandel
First change the default_controller value in route.php which lies inside config folder.
首先更改位于 config 文件夹内的 route.php 中的 default_controller 值。
$route['default_controller'] = "csv";
$route['default_controller'] = "csv";
Create a controller as csv.php
创建一个控制器为csv.php
<?php
class csv extends CI_Controller
{
public $data;
public function __construct()
{
parent::__construct();
$this->load->model('csv_model');
}
function index()
{
$this->load->view('uploadCsvView',$data);
}
function uploadData()
{
$this->csv_model->uploadData();
redirect('csv');
}
}
?>
And create a model as csv_model.php
并创建一个模型作为csv_model.php
<?php
class csv_model extends CI_Model
{
function __construct()
{
parent::__construct();
}
function uploadData()
{
$count=0;
$fp = fopen($_FILES['userfile']['tmp_name'],'r') or die("can't open file");
while($csv_line = fgetcsv($fp,1024))
{
$count++;
if($count == 1)
{
continue;
}//keep this if condition if you want to remove the first row
for($i = 0, $j = count($csv_line); $i < $j; $i++)
{
$insert_csv = array();
$insert_csv['id'] = $csv_line[0];//remove if you want to have primary key,
$insert_csv['empName'] = $csv_line[1];
$insert_csv['empAddress'] = $csv_line[2];
}
$i++;
$data = array(
'id' => $insert_csv['id'] ,
'empName' => $insert_csv['empName'],
'empAddress' => $insert_csv['empAddress'],
$data['crane_features']=$this->db->insert('tableName', $data);
}
fclose($fp) or die("can't close file");
$data['success']="success";
return $data;
}
}
And at last create a view as uploadCsvView.php
最后创建一个视图作为uploadCsvView.php
<form action="<?php echo site_url();?>csv/uploadData" method="post" enctype="multipart/form-data" name="form1" id="form1">
<table>
<tr>
<td> Choose your file: </td>
<td>
<input type="file" class="form-control" name="userfile" id="userfile" align="center"/>
</td>
<td>
<div class="col-lg-offset-3 col-lg-9">
<button type="submit" name="submit" class="btn btn-info">Save</button>
</div>
</td>
</tr>
</table>
</form>
And create mysql table where data is going to be inserted:
并创建要插入数据的 mysql 表：
CREATE TABLE tableName(
id INT,
empName VARCHAR( 100 ) ,
empAddress VARCHAR( 100 ),
PRIMARY KEY (id)
)
And most important point:
还有最重要的一点：
MySql and Csv file should both be same
MySql 和 Csv 文件应该都相同
Sample csv data is in the following link:
示例 csv 数据位于以下链接中：
https://drive.google.com/file/d/0B-OuLrage4PpUmtKNkhuS1JrSkE/view?usp=sharing
https://drive.google.com/file/d/0B-OuLrage4PpUmtKNkhuS1JrSkE/view?usp=sharing
