Welcome Guest, Not a member yet? Register   Sign In
Help about importing data from csv to mysql
#1

[eluser]Wondering Coder[/eluser]
Hi to all,

I'm been trying to upload a .csv file and save it to my table. But it gives me an error.

this is my model
Code:
function save_csv($data)
    {
        $query = $this->db->query("LOAD DATA LOCAL INFILE $data
                INTO TABLE pps_test
                FIELDS TERMINATED BY ','
                LINES TERMINATED BY '\n'
                (test_name, test_age)");
        return $query;
    }
My controller
Code:
if($save)
        {
            $config = array(
                'upload_path' => './media/csv/',
                'allowed_types' => 'csv',
                'max_size' => '100',
            );
        
            $this->load->library('upload', $config);
               if ( ! $this->upload->do_upload())
            {
                $data['error'] = $this->upload->display_errors('<p class="error">','</p>');
            }
            else
            {
            #$this->upload->do_upload();  
                $image_data = $this->upload->data();
                //print_r($image_data);
                $this->admin_db->save_csv($image_data['full_path']);
                
            }
        }

and this is the error that gives me:
Code:
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:/xampp/htdocs/dcs-pps/media/csv/Book14.csv INTO TABLE pps_tes' at line 1

LOAD DATA LOCAL INFILE C:/xampp/htdocs/dcs-pps/media/csv/Book14.csv INTO TABLE pps_test FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ' (test_name, test_age)

Filename: C:\xampp\htdocs\dcs-pps\system\database\DB_driver.php

Line Number: 330
Hope anyone can help me in here.
#2

[eluser]leonardteo[/eluser]
Never used this function before but you might need quotes around $data.

If you see the docs there are single quotes around the filename:
http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Another way to do this is to read it in using fgetcsv (http://php.net/manual/en/function.fgetcsv.php) then use active record to create each record. Probably less efficient...

L.
#3

[eluser]Wondering Coder[/eluser]
got it working already and your right by adding quotes in my variable. haha. anyway this is it for today. Time to sleep. @_@




Theme © iAndrew 2016 - Forum software by © MyBB