0
This is my csv importinf file how iam inserting data into database.

function csvimport() {
        $data['error'] = '';    //initialize image upload error array to empty

        $config['upload_path'] = './uploads/';
        $config['allowed_types'] = 'csv';
        $config['max_size'] = '1000';

        $this->load->library('upload', $config);
        // If upload failed, display error
        if (!$this->upload->do_upload()) {
            $data['error'] = $this->upload->display_errors();

        } else {
            $file_data = $this->upload->data();
            $file_path =  './uploads/'.$file_data['file_name'];

            if ($this->csvimport->get_array($file_path)) {
                $csv_array = $this->csvimport->get_array($file_path);
                foreach ($csv_array as $row) {

                    $insert_data = array(
                       'cat_id'=>$row['Category Id'], 
                        'name'=>$row['Category Name'],

                    );
                    $this->bulkupload_model->insert_csv($insert_data);

                    $insert=array(
                    'sub_cat_id'=>$row['Sub-category Id'],
                    'name'=>$row['Sub-category Name'],

                    );
                    $this->bulkupload_model->insertcsv($insert);
               }
                $this->session->set_flashdata('success', 'Csv Data Imported Succesfully');
                redirect(base_url().'uploads/index');

            } 

            }            

        }  

 This is my csv import libraryfunction       

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); 


/**
 * CodeIgniter CSV Import Class
 *
 * This library will help import a CSV file into
 * an associative array.
 * 
 * This library treats the first row of a CSV file
 * as a column header row.
 * 
 *
 * @package         CodeIgniter
 * @subpackage      Libraries
 * @category        Libraries
 * @author          Brad Stinson
 */

class Csvimport {

    private $filepath = "";
    private $handle = "";
    private $column_headers = "";

   /**
     * Function that parses a CSV file and returns results
     * as an array.
     *
     * @access  public
     * @param   filepath        string  Location of the CSV file
     * @param   column_headers  array   Alternate values that will be used for array keys instead of first line of CSV
     * @param   detect_line_endings  boolean  When true sets the php INI settings to allow script to detect line endings. Needed for CSV files created on Macs.
     * @return  array
     */
    public function get_array($filepath='', $column_headers='', $detect_line_endings=FALSE)
    {
        // If true, auto detect row endings
        if($detect_line_endings){
            ini_set("auto_detect_line_endings", TRUE);
        }

        // If file exists, set filepath
        if(file_exists($filepath))
        {
            $this->_set_filepath($filepath);
        }
        else
        {
            return FALSE;            
        }


        // If column headers provided, set them
        $this->_set_column_headers($column_headers);

        // Open the CSV for reading
        $this->_get_handle();

        $row = 0;

        while (($data = fgetcsv($this->handle, 0, ",")) !== FALSE) 
        {  



            // If first row, parse for column_headers
            if($row == 0)
            {
                // If column_headers already provided, use them
                if($this->column_headers)
                {
                    foreach ($this->column_headers as $key => $value)
                    {
                        $column_headers[$key] = trim($value);
                    }
                }
                else // Parse first row for column_headers to use
                {
                    foreach ($data as $key => $value)
                    {
                        $column_headers[$key] = trim($value);
                    }                
                }          
            }


            else
            {
                $new_row = $row - 1; // needed so that the returned array starts at 0 instead of 1
                foreach($column_headers as $key => $value) // assumes there are as many columns as their are title columns
                {
                    $result[$new_row][$value] = trim($data[$key]);
                }
            }
            $row++;
        }

        $this->_close_csv();

        return $result;
    }

   /**
     * Sets the filepath of a given CSV file
     *
     * @access  private
     * @param   filepath    string  Location of the CSV file
     * @return  void
     */
    private function _set_filepath($filepath)
    {
        $this->filepath = $filepath;
    }

   /**
     * Sets the alternate column headers that will be used when creating the array
     *
     * @access  private
     * @param   column_headers  array   Alternate column_headers that will be used instead of first line of CSV
     * @return  void
     */
    private function _set_column_headers($column_headers='')
    {
        if(is_array($column_headers) && !empty($column_headers))
        {
            $this->column_headers = $column_headers;
        }
    }

   /**
     * Opens the CSV file for parsing
     *
     * @access  private
     * @return  void
     */
    private function _get_handle()
    {
        $this->handle = fopen($this->filepath, "r");
    }

   /**
     * Closes the CSV file when complete
     *
     * @access  private
     * @return  array
     */
    private function _close_csv()
    {
        fclose($this->handle);
    }    
}

while inserting the data only unique records should be inserted into the database how can i do this.

4
Contributors
30
Replies
98
Views
3 Years
Discussion Span
Last Post by ashalatha
0

Hi,

while inserting the data only unique records should be inserted into the database

  • Unique records based on what field(s)?
  • Unique records in the csv?
    You can use array_unique() to remove duplicates from csv, before you send it to bulk upload
  • Unique records in the DB?
    I assume bulkupload_model is a custom model, you can change the functionality and check if a record exists (based on your unique field) before inserting it?
0

The code which i mentioned above is wrong or right.

It seems correct, but if you have doubts check the error log of CI and paste the errors here.

y did u use set cat_id=? what is ?

It's a query bind in a prepared statement, and it used to escape values: the values are passed to the query through an array of values (so without index keys). In your previous code, at line 23, would translate to something like this:

$insert_data = array(
                   'cat_id'=>$row['Category Id'], 
                    'name'=>$row['Category Name'],
                );

$insert_data = array_values($insert_data);

Or simply:

$insert_data = array(
    $row['Category Id'],
    $row['Category Name']
);

For more information read the last paragraph in this page:

If you have doubts, show us your Model code.

0
function insert_csv($data)
    {
        $this->db->insert('category', $data);
    }
    function insertcsv($data)
    {
        $this->db->insert('sub_category',$data);
    }

This is my model code

0

cat_id is the name which i given in database iam comparing that name with the name given in the csv file so i use d'cat_id'=$row['CategoryId']

0

Ok, in your database table create a unique key:

alter table category add unique key(cat_id);

So you cannot insert duplicates and to avoid errors add the IGNORE statement to your insert query, your model now becomes:

function insert_csv($data)
{
    $sql = "INSERT IGNORE INTO category SET cat_id = ?, name = ?"; 
    $this->db->query($sql, array_values($data));

}

And everything should work fine.

Docs: http://dev.mysql.com/doc/refman/5.5/en/constraint-primary-key.html

Edited by cereal

0

You may use on duplicate phrase, if your table has proper primary key or unique key combination. It wil not give any error on duplicate value insertion, rather it wil update it accoding to what you set to update

$sql = "insert into table (col1,col2) values (val1,val2)  on duplicate key update  col1=val1";
0

If iam uploading an empty csv file i should get a message as please insert the values how can i do this

0

at line 145 after while loop you can check $row variable

If ($row==0 )
   echo "UPLOAD FILE WITH PROPER RECORDS";
0
If ($row==0 )
   echo "UPLOAD FILE WITH PROPER RECORDS";

   if i use this code and i import an csv file it is inserting as zeros
0

attach here your php file ,sample csv file and mysql table script, so that your code can be tested here

0
Controller:

function csvimport() {
        $data['error'] = '';    //initialize image upload error array to empty
        $config['upload_path'] = './uploads/';
        $config['allowed_types'] = 'csv';
        $config['max_size'] = '1000';
        $this->load->library('upload', $config);
        // If upload failed, display error
        if (!$this->upload->do_upload()) {
            $data['error'] = $this->upload->display_errors();
        } else {
            $file_data = $this->upload->data();
            $file_path =  './uploads/'.$file_data['file_name'];
            if ($this->csvimport->get_array($file_path)) {
                $csv_array = $this->csvimport->get_array($file_path);
                foreach ($csv_array as $row) {
                    $insert_data = array(
                       'cat_id'=>$row['Category Id'], 
                        'name'=>$row['Category Name'],
                    );
                    $this->bulkupload_model->insert_csv($insert_data);
                    $insert=array(
                    'sub_cat_id'=>$row['Sub-category Id'],
                    'name'=>$row['Sub-category Name'],
                    );
                    $this->bulkupload_model->insertcsv($insert);
               }
                $this->session->set_flashdata('success', 'Csv Data Imported Succesfully');
                redirect(base_url().'uploads/index');
            } 
            }            
        }  

 csv import libraryfunction :

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); 
/**
 * CodeIgniter CSV Import Class
 *
 * This library will help import a CSV file into
 * an associative array.
 * 
 * This library treats the first row of a CSV file
 * as a column header row.
 * 
 *
 * @package         CodeIgniter
 * @subpackage      Libraries
 * @category        Libraries
 * @author          Brad Stinson
 */
class Csvimport {
    private $filepath = "";
    private $handle = "";
    private $column_headers = "";
   /**
     * Function that parses a CSV file and returns results
     * as an array.
     *
     * @access  public
     * @param   filepath        string  Location of the CSV file
     * @param   column_headers  array   Alternate values that will be used for array keys instead of first line of CSV
     * @param   detect_line_endings  boolean  When true sets the php INI settings to allow script to detect line endings. Needed for CSV files created on Macs.
     * @return  array
     */
    public function get_array($filepath='', $column_headers='', $detect_line_endings=FALSE)
    {
        // If true, auto detect row endings
        if($detect_line_endings){
            ini_set("auto_detect_line_endings", TRUE);
        }
        // If file exists, set filepath
        if(file_exists($filepath))
        {
            $this->_set_filepath($filepath);
        }
        else
        {
            return FALSE;            
        }
        // If column headers provided, set them
        $this->_set_column_headers($column_headers);
        // Open the CSV for reading
        $this->_get_handle();
        $row = 0;
        while (($data = fgetcsv($this->handle, 0, ",")) !== FALSE) 
        {  
            // If first row, parse for column_headers
            if($row == 0)
            {
                // If column_headers already provided, use them
                if($this->column_headers)
                {
                    foreach ($this->column_headers as $key => $value)
                    {
                        $column_headers[$key] = trim($value);
                    }
                }
                else // Parse first row for column_headers to use
                {
                    foreach ($data as $key => $value)
                    {
                        $column_headers[$key] = trim($value);
                    }                
                }          
            }
            else
            {
                $new_row = $row - 1; // needed so that the returned array starts at 0 instead of 1
                foreach($column_headers as $key => $value) // assumes there are as many columns as their are title columns
                {
                    $result[$new_row][$value] = trim($data[$key]);
                }
            }
            $row++;
        }
        $this->_close_csv();
        return $result;
    }
   /**
     * Sets the filepath of a given CSV file
     *
     * @access  private
     * @param   filepath    string  Location of the CSV file
     * @return  void
     */
    private function _set_filepath($filepath)
    {
        $this->filepath = $filepath;
    }
   /**
     * Sets the alternate column headers that will be used when creating the array
     *
     * @access  private
     * @param   column_headers  array   Alternate column_headers that will be used instead of first line of CSV
     * @return  void
     */
    private function _set_column_headers($column_headers='')
    {
        if(is_array($column_headers) && !empty($column_headers))
        {
            $this->column_headers = $column_headers;
        }
    }
   /**
     * Opens the CSV file for parsing
     *
     * @access  private
     * @return  void
     */
    private function _get_handle()
    {
        $this->handle = fopen($this->filepath, "r");
    }
   /**
     * Closes the CSV file when complete
     *
     * @access  private
     * @return  array
     */
    private function _close_csv()
    {
        fclose($this->handle);
    }    
}

Model:

function insert_csv($data)
    {
        $this->db->insert('category', $data);
    }
    function insertcsv($data)
    {
        $this->db->insert('sub_category',$data);
    }

Iam loading the csv import library function in my controller

0

Category Table

cat_id
name

Sub-category

sub_cat_id(primary key)
name

while uploading the file sub category ids should be unique if we enter duplicate it should not add and should not display error just it should show a message as format is wrong.

0
function insert_csv($data)
{
    $query = $this->db->query("select count(*) cnt from category where cat_id='{$data['cat_id']}'");
    $row = $query->first_row();

    if(trim($data['cat_id'])!="" && $row->cnt==0)
        $this->db->insert('category', $data);
}
function insertcsv($data)
{

    $query = $this->db->query("select count(*) cnt from sub_category where sub_cat_id='{$data['sub_cat_id']}'");
    $row = $query->first_row();

    if(trim($data['sub_cat_id'])!="" && $row->cnt==0)   
        $this->db->insert('sub_category',$data);
}

Edited by urtrivedi

0

How to concanate two columns into a single column while uploading a csv file.

0

can any one help me how to concatenate two columns of one row into a single column of another row while uploading a csv file in php

0

how to delete the data from multiple tables ,for example if the id present in one table and the same id is present in another table if we delete the id from first table we should get a alert message as first delete the id in that table how can we do this can any one help me

0

@ashalatha

hi, you should open new threads for each new question not directly related to the original.

0

how to concatenate 2nd row of two columns into a one column of a 1st row in php while uploading a csv file

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.