如何使用codeigniter框架在jquery中读取csv文件 [英] how to read csv file in jquery using codeigniter framework
本文介绍了如何使用codeigniter框架在jquery中读取csv文件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
假设这是我的csv文件
fileempId，lastName，firstName，middleName，street1，street2，city，state，zip ，gender，birthDate，ssn，empStatus，joinDate，workStation，location，custom1，workState，salary，payFrequency，FITWStatus，FITWExemptions，DD1Routing，DD1Account，DD1Amount，DD1AmountCode，DD1Checking，DD2Routing，DD2Account，DD2Amount，DD2AmountCode，DD2Checking
1，Dela Cruz，Juano，Santos ,,,,, 1 ,,, Part Time Internship，asd Division，Makati，one，asd，150，Bi Weekly，不适用，100 ,,,,,, 1234,9876 ，100，SAVINGS，BLANK
3，Palogan，Ralph ,,,,,,, 1,11-Mar-11，，全职合同，2-Mar-11，sdf Department，pasay ,, ,,,不适用,,,,,,,,,,, 5，圣，u ,,,暗叶,,, 1,11-Mar-11 ,,,,,,,,,,不适用，0 ,, ,,,,,,,,
这是我的表单
< label>选择文件：< / label>< font color =＃FF0000> *< / font&
< input type =filename =fileid =file/>
< input type =buttonid =importButtonvalue =Importname =importButton/>
如何读取csv中的数据并将其存储到mysql数据库任何关于如何做的示例代码。
请看下面的代码有什么问题，..，
我的查看页面包含jquery和窗体,,。
< td>< label& ; / label>< font color =＃FF0000> *< / font>< / td>
< td>< input type =filename =fileid =file/>< / td>
< td>< label>导入类型< / label>< font color =＃FF0000> *< / font>< / td&
< td>< select name =importnameid =importnamestyle =width：130px;>
< option value =selected> - 选择 - < / option>
<？php
foreach（$ fields as $ data）{
print'< option value ='。$ data-> import_id。'>'。$ data - > name。'< / option>';
}
？>
< / select>< / td>
< script type =text / javascript>
$（function（）{
$（＃importData）。click（function（e）{
var file = $（'＃file'）。 $ b var type = $（'＃importname'）。val（）;
$ .post（<？php print base_url（）。'index.php / MyController / readExcel'？& file：file，type：type}，
function（data）
{
if（data！='success'）
{
error_message（data）;
}
else {
alert（Upload Succcessfull！）;
}
}）;
}
}）;
< / script>
我的控制器
function readExcel（）{
$ file = $ this-> input-> post（'file'）;
$ type = $ this-> input-> post（'type'）;
$ this-> load-> library（'csvreader'）;
$ result = $ this-> csvreader-> parse_file（$ file）;
$ data ['csvData'] = $ result;
$ this-> load-> view（'MyViews / showImportFile'，$ data）;
}
我的图书馆
<？php if（！defined（'BASEPATH'））exit（'不允许直接脚本访问）;
class csvreader {
var $ fields; / **解析后检索的列名称* /
var $ separator =';'; / **分隔符用于展开每行* /
var $ enclosure =''; / **用于装饰每个字段的机箱* /
var $ max_row_size = 4096; / * *用于解码的最大行大小* /
function parse_file（$ p_Filepath）{
$ file = fopen（$ p_Filepath，'r'）;
$ this-> fields = fgetcsv（$ file，$ this-> max_row_size，$ this-> separator，$ this-> enclosure）;
$ keys_values = explode（'，'，$ this - > fields [0]）;
$ content = array（）;
$ keys = $ this-> escape_string（$ keys_values）;
$ i = 1;
while（（$ row = fgetcsv（$ file，$ this-> max_row_size，$ this-> separator，$ this-> enclosure））！= false）{
if（$ row！= null）{//跳过空行
$ values = explode（'，'，$ row [0]）; $ values））{
$ arr = array（）;
$ new_values = array（）;
$ new_values = $ this-> escape_string（$ values）;
for（$ j = 0; $ j< count（$ keys）; $ j ++）{
if（$ keys [$ j]！=）{
$ arr [$ keys [$ j]] = $ new_values [$ j];
}
}
$ content [$ i] = $ arr;
$ i ++;
}
}
}
fclose（$ file）;
return $ content;
}
function escape_string（$ data）{
$ result = array（）;
foreach（$ data as $ row）{
$ result [] = str_replace（''，''，$ row）;
}
return $ result;
}
}
遇到错误
遇到PHP错误
严重性：警告
消息：fopen（export 1.csv）：无法打开流：没有此类文件或目录
文件名：libraries / csvreader.php <
解决方案
根据您的评论，实际上您想要上传您的CSV文件首先到您的服务器，然后检查CSV字段/标头是否匹配您的数据库表字段之一插入行，这里我做的示例代码：
CSV阅读器（基于此 图书馆 进行一些调整）：
<？php if（！defined 'BASEPATH'））exit（'不允许直接脚本访问'）;
/ **
* CSVReader类
*
* $ Id：csvreader.php 147 2007-07-09 23：12：45Z Pierre-Jean $
*
*允许将CSV文件内容检索为二维数组。
*第一个文本行应包含列名。
*
* @author Pierre-Jean Turpeau
* @link http://www.codeigniter.com/wiki/CSVReader
* /
class CSVReader {
var $ fields; / **解析后检索的列名称* /
var $ separator ='，'; / **用于展开每行的分隔符* /
/ **
*解析包含CSV格式数据的文本。
*
* @access public
* @param string
* @return array
* /
function parse_text（$ p_Text）{
$ lines = explode（\\\
，$ p_Text）;
return $ this-> parse_ lines（$ lines）;
}
/ **
*解析包含CSV格式数据的文件。
*
* @access public
* @param string
* @return array
* /
function parse_file（$ p_Filepath）{
$ lines = file（$ p_Filepath）;
return $ this-> parse_lines（$ lines）;
}
/ **
*解析包含CSV格式数据的文本行数组。
*
* @access public
* @param array
* @return array
* /
function parse_lines（$ p_CSVLines）{
$ content = FALSE;
foreach（$ p_CSVLines as $ line_num => $ line）{
if（$ line！=''）{//跳过空行
$ line = trim
$ elements = explode（$ this-> separator，$ line）;
if（！is_array（$ content））{//第一行包含字段name
$ this-> fields = $ elements;
$ content = array（）;
} else {
$ item = array（）;
foreach（$ this-> fields as $ id => $ field）{
if（isset（$ elements [$ id]））{
$ item [$ field] = $ elements [$ id];
}
}
$ content [] = $ item;
}
}
}
return $ content;
}
/ **
*获取字段
* /
public function get_fields（）{
return $ this-> fields ;
}
}
在CI上执行上传处理常式：
public function do_upload（）
{
$ config ['upload_path'] ='./uploads/';
$ config ['allowed_types'] ='csv';
$ config ['max_size'] ='100';
$ config ['max_width'] ='1024';
$ config ['max_height'] ='768';
$ this-> load-> library（'upload'，$ config）;
if（！$ this-> upload-> do_upload（'csvfile'））
{
$ error = array（'error'=> $ this- > upload-> display_errors（））;
$ this-> load-> view（'csv_upload'，$ error）;
}
else
{
$ upload_data = $ this-> upload-> data（）;
//开始读取CSV文件
$ this-> load-> library（'csvreader'）;
$ file_path = $ upload_data ['full_path'];
$ csv_data = $ this-> csvreader-> parse_file（$ file_path）;
$ csv_fields = $ this-> csvreader-> get_fields（）;
//列出数据库表
$ tables = $ this-> db-> list_tables（）;
foreach（$ tables as $ table）
{
$ fields = $ this-> db-> list_fields（$ table）;
if（$ fields == $ csv_fields）//与数据库表匹配
{
//插入记录
foreach（$ csv_data as $ row ）{
$ this-> db-> insert（$ table，$ row）;
}
}
}
$ data = array（
'upload_data'=> $ upload_data，
'csv_data'=> ; $ csv_data，
）;
$ this-> load-> view（'upload_success'，$ data）;
}
}
下载完整示例 此处 。您可以在文件夹 uploads 中找到CSV文件示例。
suppose this is my csv file
fileempId,lastName,firstName,middleName,street1,street2,city,state,zip,gender,birthDate,ssn,empStatus,joinDate,workStation,location,custom1,workState,salary,payFrequency,FITWStatus,FITWExemptions,DD1Routing,DD1Account,DD1Amount,DD1AmountCode,DD1Checking,DD2Routing,DD2Account,DD2Amount,DD2AmountCode,DD2Checking
1,Dela Cruz,Juano,Santos,,,,,,1,,,Part Time Internship,, asd Division, Makati,one, asd,150,Bi Weekly,Not Applicable,100,,,,,,1234,9876,100,SAVINGS,BLANK
3,Palogan,Ralph,,,,,,,1,11-Mar-11,,Full Time Contract,2-Mar-11, sdf Department, pasay,, ,,,Not Applicable,,,,,,,,,,, 5,San,Goku,,,,hidden leaf,,,1,11-Mar-11,,,,,,,,,,Not Applicable,0,,,,,,,,,,
this is my form
<label>Choose File:</label><font color="#FF0000">*</font>
<input type="file" name="file" id="file" />
<input type="button" id="importButton" value="Import" name="importButton" />
how to read the data in csv and store it to mysql database(codeigniter)? Any example code on how to do it,.
please see what is wrong with my code below,..,
my view page containing the jquery and form,,.
<td><label>Choose File:</label><font color="#FF0000">*</font></td>
<td><input type="file" name="file" id="file" /></td>
<td><label>Import Type </label><font color="#FF0000">*</font></td>
<td><select name="importname" id="importname" style="width:130px;">
<option value="" selected>--Select--</option>
<?php
foreach($fields as $data){
print '<option value="'.$data->import_id.'">'.$data->name.'</option>';
}
?>
</select></td>
<script type="text/javascript">
$(function() {
$("#importData").click(function(e) {
var file = $('#file').val();
var type = $('#importname').val();
$.post("<?php print base_url().'index.php/MyController/readExcel'?>",{file: file, type: type},
function(data)
{
if(data!='success')
{
error_message(data);
}
else{
alert("Upload Succcessfull!");
}
});
});
});
</script>
my controller
function readExcel(){
$file=$this->input->post('file');
$type=$this->input->post('type');
$this->load->library('csvreader');
$result = $this->csvreader->parse_file($file);
$data['csvData'] = $result;
$this->load->view('MyViews/showImportFile', $data);
}
my library
<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
class csvreader {
var $fields; /** columns names retrieved after parsing */
var $separator = ';'; /** separator used to explode each line */
var $enclosure = '"'; /** enclosure used to decorate each field */
var $max_row_size = 4096; /** maximum row size to be used for decoding */
function parse_file($p_Filepath) {
$file = fopen($p_Filepath, 'r');
$this->fields = fgetcsv($file, $this->max_row_size, $this->separator, $this->enclosure);
$keys_values = explode(',',$this->fields[0]);
$content = array();
$keys = $this->escape_string($keys_values);
$i = 1;
while( ($row = fgetcsv($file, $this->max_row_size, $this->separator, $this->enclosure)) != false ) {
if( $row != null ) { // skip empty lines
$values = explode(',',$row[0]);
if(count($keys) == count($values)){
$arr = array();
$new_values = array();
$new_values = $this->escape_string($values);
for($j=0;$j<count($keys);$j++){
if($keys[$j] != ""){
$arr[$keys[$j]] = $new_values[$j];
}
}
$content[$i]= $arr;
$i++;
}
}
}
fclose($file);
return $content;
}
function escape_string($data){
$result = array();
foreach($data as $row){
$result[] = str_replace('"', '',$row);
}
return $result;
}
}
i am getting an error
A PHP Error was encountered
Severity: Warning
Message: fopen(export 1.csv): failed to open stream: No such file or directory
Filename: libraries/csvreader.php
can anybody out there help me please!!!
解决方案
Based on your comment that actually you want to upload your CSV file first to your server then check if the CSV fields/headers match to one of your database table fields to insert the row, here the sample code I made:
CSV Reader (based from this library with some adjustments):
<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
/**
* CSVReader Class
*
* $Id: csvreader.php 147 2007-07-09 23:12:45Z Pierre-Jean $
*
* Allows to retrieve a CSV file content as a two dimensional array.
* The first text line shall contains the column names.
*
* @author Pierre-Jean Turpeau
* @link http://www.codeigniter.com/wiki/CSVReader
*/
class CSVReader {
var $fields; /** columns names retrieved after parsing */
var $separator = ','; /** separator used to explode each line */
/**
* Parse a text containing CSV formatted data.
*
* @access public
* @param string
* @return array
*/
function parse_text($p_Text) {
$lines = explode("\n", $p_Text);
return $this->parse_lines($lines);
}
/**
* Parse a file containing CSV formatted data.
*
* @access public
* @param string
* @return array
*/
function parse_file($p_Filepath) {
$lines = file($p_Filepath);
return $this->parse_lines($lines);
}
/**
* Parse an array of text lines containing CSV formatted data.
*
* @access public
* @param array
* @return array
*/
function parse_lines($p_CSVLines) {
$content = FALSE;
foreach( $p_CSVLines as $line_num => $line ) {
if( $line != '' ) { // skip empty lines
$line = trim($line);
$elements = explode($this->separator, $line);
if( !is_array($content) ) { // the first line contains fields names
$this->fields = $elements;
$content = array();
} else {
$item = array();
foreach( $this->fields as $id => $field ) {
if( isset($elements[$id]) ) {
$item[$field] = $elements[$id];
}
}
$content[] = $item;
}
}
}
return $content;
}
/**
* Get fields
*/
public function get_fields(){
return $this->fields;
}
}
Do upload handler on CI:
public function do_upload()
{
$config['upload_path'] = './uploads/';
$config['allowed_types'] = 'csv';
$config['max_size'] = '100';
$config['max_width'] = '1024';
$config['max_height'] = '768';
$this->load->library('upload', $config);
if ( ! $this->upload->do_upload('csvfile'))
{
$error = array('error' => $this->upload->display_errors());
$this->load->view('csv_upload', $error);
}
else
{
$upload_data = $this->upload->data();
// start to read the CSV file
$this->load->library('csvreader');
$file_path = $upload_data['full_path'];
$csv_data = $this->csvreader->parse_file($file_path);
$csv_fields = $this->csvreader->get_fields();
// list your database table
$tables = $this->db->list_tables();
foreach($tables as $table)
{
$fields = $this->db->list_fields($table);
if($fields == $csv_fields) // match to one of database table
{
// insert the record
foreach($csv_data as $row){
$this->db->insert($table, $row);
}
}
}
$data = array(
'upload_data' => $upload_data,
'csv_data' => $csv_data,
);
$this->load->view('upload_success', $data);
}
}
Download full sample HERE. You can find sample of CSV file inside folder uploads.
这篇关于如何使用codeigniter框架在jquery中读取csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
