Importing data from csv to mysql

Question

hey guys i am new in codeigniter. I am using codeigniter for this project. I’m been trying to upload a .csv file and save it to my table. But it gives me an Database error. i am using codeigniter framework of php.

model:

  public function save_csv($data)
        {
            $infile=$data['upload_data']['full_path'];
            print_r($infile);
            $query = $this->db->query("LOAD DATA INFILE '".$infile."'
                    INTO TABLE promotion_product
                    FIELDS TERMINATED BY ','
                    ESCAPED BY '\\' 
                    LINES TERMINATED BY '\r\n'
                    IGNORE 1 LINES
                    ");


            print_r($query);
            return $query;
        }

controller:

  public function upload() {
            $image_path = realpath(APPPATH . '../uploads');
             //$file_path =  $image_path.$file_data['file_name'];
            $config['upload_path'] = $image_path;
            $config['allowed_types'] = '*';
            $config['max_size'] = '100';
            $config['max_width'] = '1024';
            $config['max_height'] = '768';

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

            if (!$this->upload->do_upload()) {
                $error = array('error' => $this->upload->display_errors());

                $this->load->view('upload_form', $error);
            } else {

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

                //$pramod=$data[upload_data][full_path];
                //print_r($pramod);
                $this->load->view('upload_success', $data);
                $this->usermodel->save_csv($data);
            }
        }

and this is the error that gives me:

A Database Error Occurred

    Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'C:/wamp/www/mapApplication/uploads/pramotion113.csv INTO TABLE ' at line 1

LOAD DATA INFILE C:/wamp/www/mapApplication/uploads/pramotion113.csv INTO TABLE promotion_product FIELDS TERMINATED BY ',' ESCAPED BY '\' LINES TERMINATED BY ' ' IGNORE 1 LINES

Filename: C:\wamp\www\mapApplication\system\database\DB_driver.php

Line Number: 330

If the file is generated in Windows you may need to use: LINES TERMINATED BY '\r\n' otherwise it depends. — Marco Mura, Dec 09 '14 at 16:33
http://dev.mysql.com/doc/refman/5.1/en/load-data.html ... It says maybe that you need to use the \r if the file isn't generated on windows — Marco Mura, Dec 09 '14 at 16:36
Try `LOAD DATA INFILE '$infile'` or `LOAD DATA INFILE '".$infile."'` - Should most likely be wrapped in quotes. — Funk Forty Niner, Dec 09 '14 at 16:48

score 1 · Accepted Answer · edited May 23 '17 at 12:27

1

Tools like SequelPro or PHPmyAdmin do this with ease, or is there a specific reason you want to it within CodeIgniter?

Edit:

This post seems to have lots of info related to what you're trying to achive: PHP - Import CSV file to mysql database Using LOAD DATA INFILE

edited May 23 '17 at 12:27

Community

1
1

answered Dec 09 '14 at 16:54

charj

339
3
13

This should be a comment, not an answer. Although I realize you don't have enough rep yet to comment. An answer should probably never contain a question. – Dec 09 '14 at 17:10
I understand that, I'm very new to stackoverflow and you're correct in saying, at the time of posting, I did not have enough rep to comment - I guess due to me being able to comment, I now have the rep to comment. :) – charj Dec 09 '14 at 17:38

Importing data from csv to mysql

1 Answers1