CODEWITHSUNDEEP
phpmysqlcodeigniter
Rajan
Rajan

Reputation: 2425

how to import tab separated data from text file into MySQL database in codeigniter?

I am having a .dat extension file which contains information of my bio-metric machine. Now i want to upload that file to my server and want to import its data into mysql database.

The File looks like this: my file

Now my table is like this: database

I want to insert the data into mysql database using CodeIgniter's file upload.

I have done this in core php but i cant do this in CI so please guide me here.

This is my pure PHP code:

<?php
include_once 'dbconfig.php';
if(isset($_POST['btn-upload']))
{    
    $file = rand(1000,100000)."-".$_FILES['file']['name'];
    $file_loc = $_FILES['file']['tmp_name'];
    $file_size = $_FILES['file']['size'];
    $file_type = $_FILES['file']['type'];
    $folder="uploads/";
    $location =$_FILES['file'];

    // new file size in KB
    $new_size = $file_size/1024;  
    // new file size in KB

    // make file name in lower case
    $new_file_name = strtolower($file);
    // make file name in lower case

    $final_file=str_replace(' ','-',$new_file_name);

    if(move_uploaded_file($file_loc,$folder.$final_file))
    {
        $sql="INSERT INTO tbl_uploads(file,type,size) VALUES('$final_file','$file_type','$new_size')";
        mysqli_query($connection,$sql);

        $handle = fopen("c:/wamp/www/codeigniter/uploads/$file", "r");

        if ($handle) {
        while (($line = fgets($handle)) !== false) {

                $lineArr = explode("\t", "$line");
                //var_dump($lineArr); // to make sure array is ok

                // instead assigning one by onb use php list -> http://php.net/manual/en/function.list.php            
                list($emp_id, $date_data, $abc, $def, $entry, $ghi) = $lineArr;

                // and then insert data
                mysqli_query($connection,"INSERT INTO `daily_data2` (emp_id, date_data, abc, def, entry, ghi) 
                VALUES ('$emp_id', '$date_data', '$abc', '$def', '$entry', '$ghi')");
            }
            fclose($handle);
        }
        ?>
        <script>
            alert('successfully uploaded');
            window.location.href='index.php?success';
        </script>
        <?php
    }
    else
    {
    ?>
        <script>
        alert('error while uploading file');
        window.location.href='index.php?fail';
        </script>
    <?php
    }
}
?>

My New CI Code:

<?php
class Attendance extends Admin_Controller
 {

public function __construct()
{

    parent::__construct();
    $this->load->library('upload');
    $this->load->helper(array('form', 'url'));

}


public function index()
{

    $this->data['subview'] = 'admin/attendance/index';
    $this->load->view('admin/_layout_main', $this->data);
}



public function upload()
{ if (isset($_POST['btn-upload'])) {

        $file = rand(1000, 100000) . "-" . $_FILES['file']['name'];
        $file_loc = $_FILES['file']['tmp_name'];
        $file_size = $_FILES['file']['size'];
        $file_type = $_FILES['file']['type'];
        $folder = "uploads/";
        $location = $_FILES['file'];


        $new_size = $file_size / 1024; // new file size in KB
        $new_file_name = strtolower($file);
        $final_file = str_replace(' ', '-', $new_file_name); // make file name in lower case

        if (move_uploaded_file($file_loc, $folder . $final_file)) {

            //Prepare upload data
            $upload_data = Array(
                'file'  => $final_file,
                'type'  => $file_type,
                'size'  => $new_size
            );
            //Insert into tbl_uploads
            $this->db->insert('daily_data2', $upload_data);

            $handle = fopen("c:/wamp/www/codeigniter/uploads/$file", "r");

            if ($handle) {
                while (($line = fgets($handle)) !== false) {

                    $lineArr = explode("\t", "$line");
                    // instead assigning one by onb use php list -> http://php.net/manual/en/function.list.php
                    list($emp_id, $date_data, $abc, $def, $entry, $ghi) = $lineArr;

                    $daily_data = Array(
                        'emp_id'    => $emp_id,
                        'date_data' => $date_data,
                        'abc'       => $abc,
                        'def'       => $def,
                        'entry'     => $entry,
                        'ghi'       => $ghi
                    );

                    //Insert data
                    $this->db->insert('daily_data2', $daily_data);
                }
                fclose($handle);
            }
            //Alert success redirect to ?success
            $this->alert('successfully uploaded', 'index.php?success');
        } else {
            //Alert error
            $this->alert('error while uploading file', 'index.php?fail');
        }
    }


}




protected function alert($text, $location) {
    return "<script> alert('".$text."'); window.location.href='".$location."'; </script>";
}


}

The CI Upload Form:

     <h2>Upload CSV To Import Users</h2>
        <form method="post" action="<?php echo site_url('admin/attendance/upload');?>" enctype="multipart/form-data">
            <input type="file" name="userfile"  ><br><br>
            <input type="submit" name="submit" value="UPLOAD" class="btn btn-primary">
        </form>

The Error error

Upvotes: 2

Views: 2470

Answers (1)

Ilanus
Ilanus

Reputation: 6928

This is untested but you can get the idea. create a new Controller file name Import.php and post this code:

CODE UPDATED

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

class Attendance extends Admin_Controller
{
    public function __construct()
    {
        parent::__construct();
        $this->load->library('upload');
        $this->load->helper(array('form', 'url'));
    }


    public function index()
    {
        $this->load->view('admin/_layout_main');
    }


    public function upload()
    { 
        if (isset($_POST['btn-upload'])) {

        $file = rand(1000, 100000) . "-" . $_FILES['file']['name'];
        $file_loc = $_FILES['file']['tmp_name'];
        $file_size = $_FILES['file']['size'];
        $file_type = $_FILES['file']['type'];
        $folder = "uploads/";
        $location = $_FILES['file'];


        $new_size = $file_size / 1024; // new file size in KB
        $new_file_name = strtolower($file);
        $final_file = str_replace(' ', '-', $new_file_name); // make file name in lower case

        if (move_uploaded_file($file_loc, $folder . $final_file)) {

            //Prepare upload data
            $upload_data = Array(
                'file'  => $final_file,
                'type'  => $file_type,
                'size'  => $new_size
            );
            //Insert into tbl_uploads
            $this->db->insert('daily_data2', $upload_data);

            $handle = fopen("c:/wamp/www/codeigniter/uploads/$file", "r");

            if ($handle) {
                while (($line = fgets($handle)) !== false) {

                    $lineArr = explode("\t", "$line");
                    // instead assigning one by onb use php list -> http://php.net/manual/en/function.list.php
                    list($emp_id, $date_data, $abc, $def, $entry, $ghi) = $lineArr;

                    $daily_data = Array(
                        'emp_id'    => $emp_id,
                        'date_data' => $date_data,
                        'abc'       => $abc,
                        'def'       => $def,
                        'entry'     => $entry,
                        'ghi'       => $ghi
                    );

                    //Insert data
                    $this->db->insert('daily_data2', $daily_data);
                }
                fclose($handle);
            }
            //Alert success redirect to ?success
            $this->alert('successfully uploaded', 'index.php?success');
            } else {
                //Alert error
                $this->alert('error while uploading file', 'index.php?fail');
            }
        }
    }

    protected function alert($text, $location) {
        return "<script> alert('".$text."'); window.location.href='".$location."'; </script>";
    }

}

View updated...

<h2>Upload CSV To Import Users</h2>
<!-- in the action you need to place /controller/function in our case @Attendance, @upload -->
<form method="post" action="<?php echo site_url('attendance/upload');?>" enctype="multipart/form-data">
    <input type="file" name="file" id="file">
    <p>
    <input type="submit" name="btn-upload" value="btn-upload" id="btn-upload" class="btn btn-primary">
</form>

Navigate to your domain/Import See what you get. Good luck

NOTES

  1. Make sure you allow folder writing permissions
  2. Make sure you have the proper HTML to handle the function.

Upvotes: 2

Related Questions