How to import CSV Data into MYSQL database using PHP CodeIgniter?

Question

Hi I have seen all over stack but couldn't find any appropriate answer for my question. The Answer were on how to read csv format and not to import into MYSQL Database.

I have a Upload Controller That uploads my file on my server. Now i want that uploaded file to be imported into MYSQL database. Please Help Me.

The Controller File:

public function upload_it() {
    //load the helper
    $this->load->helper('form');

    //Configure
    //set the path where the files uploaded will be copied. NOTE if using linux, set the folder to permission 777
    $config['upload_path'] = 'application/views/uploads/';

// set the filter image types
    $config['allowed_types'] = 'gif|csv';

    //load the upload library
    $this->load->library('upload', $config);

$this->upload->initialize($config);

$this->upload->set_allowed_types('*');

    $data['upload_data'] = '';

    //if not successful, set the error message
    if (!$this->upload->do_upload('userfile')) 
    {
        $data = array('msg' => $this->upload->display_errors());

    } 
    else
    { 

            //else, set the success message
            $data = array('msg' => "Upload success!");

            $data['upload_data'] = $this->upload->data();   

            if($_POST)
            {
                 if (($_FILES[csv][size] > 0 ) && ( $_FILES[csv][type]=="text/csv") ) 
                {
                        //get the csv file 
                        $file = $_FILES[csv][tmp_name]; 
                        $filetype=$_FILES[csv][type];
                        $handle = fopen($file, "r");
                        $i = 0;
                        $temp=str_getcsv($handle,"\n");
                        error_log("===$temp==");


                        $data = $this->user_m->('sip_id', 'sip_pass', 'name', 'key', 'email', 'password', 'phone', 'status', 'created', 'balance');


                        //Dont Know what to do next. 



                }

            }










    }

    //load the view/upload.php
    $this->load->view('admin/user/upload', $data);

}

Now I think should be creating a model that imports this uploaded file. But I dont know how to do that

Edit:

I know how to do this in php to make a connection to mysql:

<?php 
$servername = "localhost"; 
$username = "username"; 
$password = "password"; 

// Create connection 
$conn = new mysqli($servername, $username, $password); 

// Check connection 
if ($conn->connect_error) { 
die("Connection failed: " . $conn->connect_error); 
} 
echo "Connected successfully"; 
?>

what do you mean read but not import? You mean you found nothing that would read a csv and import the data into tables ? — Drew, Sep 14 '15 at 05:20
@Drew See i saw an answer that showed what was the content of csv. but i want that when i upload the file it data gets imported dynamically into database — Rajan, Sep 14 '15 at 05:21
almost like a photo import, upload, I mean, followed by an import — Drew, Sep 14 '15 at 05:22
@Drew i upload it to server then i want the csv data to be in my database. See i have users table so i want that admin can import the csv data into mysql and can create multipe users at a time — Rajan, Sep 14 '15 at 05:23
and it definitely needs to be an upload to begin with, because it is easier than him, say, just running a cron job or something ? The guy is admin afterall, but you want a cute interface for it — Drew, Sep 14 '15 at 05:24
my uploads work perfectly but how will the data from csv file get to be stored in my users table? — Rajan, Sep 14 '15 at 05:25
ok, cool, you know what the path is to it already, and is it linux ? — Drew, Sep 14 '15 at 05:26
@Drew Yes m on linux and i store my uploaded file in views folder — Rajan, Sep 14 '15 at 05:26
check out one of my answers [here](http://stackoverflow.com/a/31374053/1816093). You can fully path to the file like `'/full/path/to/file/myfile.txt'` — Drew, Sep 14 '15 at 05:30
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/89546/discussion-between-rajan-and-drew). — Rajan, Sep 14 '15 at 05:31
I have a folder called view which has the upload file say one.csv — Rajan, Sep 14 '15 at 05:56

Drew · Accepted Answer · 2015-09-14T06:14:25.827

Ok, as you told me through chat that you can call a php file through exec, and CI raw sql is new to you (and trust me I don't know CI at all)....

And as you have stated that the file name is going to be known from, let's call it a php file with a form, and that the file has already been uploaded to the view folder in some known hierarchy, then consider the following string:

LOAD DATA INFILE '/full/path/to/view/myfile.txt' 
INTO TABLE users  
    FIELDS TERMINATED BY ',' 
           OPTIONALLY ENCLOSED BY '"'
    LINES  TERMINATED BY '\n'

Yes, that will be one big string in php. So it will be like any other string, like a select statement. After you connect with mysqli (as you showed me, and I edited the question), then execute it !

If the filename coming into the PHP $_POST needs to be concatenated into the blue block above, then that is what needs to happen. That single quote after the filename is critical, trust me.

From the Manual page for Load Data

How to import CSV Data into MYSQL database using PHP CodeIgniter?

Edit:

1 Answers1

Linked