how to read csv file in jquery using codeigniter f

问题:

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!!!

回答1:

I use this library to convert array to some other data format or vice versa.

CI - Rest Server

There you can find library/class Format (Format.php) that you can use to convert CSV to array then save it into your database. This class also support other format:

• xml – almost any programming language can read XML
• json – useful for JavaScript and increasingly PHP apps.
• csv – open with spreadsheet programs
• html – a simple HTML table
• php – Representation of PHP code that can be eval()’ed
• serialize – Serialized data that can be unserialized in PHP

EDIT:

This library works on CSV with delimiter "\n" on each row and "," on each of column, you can use it like this:

$this->load->library('format');

$string_csv = "YOUR CSV";        
$result = $this->format->factory($string_csv, 'csv')->to_array();

var_dump($result);

Just that simple. However as I said above if your have another delimiter then you have to adjust the library as your need. Here the main function to convert CSV to array:

function _from_csv($string)
{
    $data = array();

    // Splits
    $rows = explode("\n", trim($string));
    $headings = explode(',', array_shift($rows));
    foreach ($rows as $row)
    {
        // The substr removes " from start and end
        $data_fields = explode('","', trim(substr($row, 1, -1)));

        if (count($data_fields) == count($headings))
        {
            $data[] = array_combine($headings, $data_fields);
        }
    }

    return $data;
}

EDIT 2:

My example will work on this standard CSV format:

Heading1, Heading2, Heading3
"1","John","London"
"2","Brian","Texas"

回答2:

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.

回答3:

I have tried your CSVReader function. It gives me perfect result as per my need. I also like your idea to check if the CSV fields/headers match to one of your database table fields to insert the row. It gives result array as below.

Array
(
    [0] => Array
        (
            [Years] => 1888
            [Make1] => Acura
            [Make2] => Honda
            [Make] => Honda
            [] => Honda
            [Makes] => Toyota
)

I have also tried CSVReader library at this location. https://github.com/bcit-ci/CodeIgniter/wiki/CSVReader But it gives result array with values imploded by comma(,) as below.

Array
(
    [0] => Array
        (
            [Years,Make1,Make2,Make,,Makes,Make] => 1888,Acura,Honda,Toyota,Honda,Toyota,Honda
        )
)

Now SubRed, in your CSVReader function, I have made changes to CSV file's first row name with whitespace at the end. But after that in the CI controller, while I am checking that fields/headers/column_name exists in CSV file with array_key_exists('column_name',$csvdata), it returns false/none because of whitespace in the column_name field at the end. So I have updated your code.

Please refer below updated code and give me any suggestions if any.

EDIT to the foreach loop in parse_lines function =>

foreach( $this->fields as $id => $field ) {  
    //echo $id;
    //echo $field;
    //In CSV File, trim(remove both end whitespaces) the first line which contains fields names
    $field = trim($field);
    if( isset($elements[$id]) ) 
    {
        $item[trim($field)] = trim($elements[$id]);//In CSV File, trim all the file data
    }
 }