mysql, php

Import CSV data into to MySQL database


Import CSV to MySQL

A simple in-built command to import whole CVS data to mysql database

1 LOAD DATA LOCAL INFILE 'd:\\\my_file.csv'
2 into TABLE table_name
3 FIELDS TERMINATED BY ','
4 LINES TERMINATED BY '\n'
5 (field1,field2,field3)

Note:
table column should be as tat of cvs file

“\\\” is important, keep it as it is just change ur file name.

 

after importing data you may found there is some extra quote in the fileds (“) you can remove it via following script

// I am using Codeigniter active record pattern hope you guys get the concept.

function read()
{

// get the data from table
$res = $this->db->select()->get(‘tbl_states_details’)->result();
$cnt = 0;

// loop it
foreach($res as $key => $val):
$cnt++;
// filter ” to null space or nothing
$data[‘suburb’] = str_replace(‘”‘,”,$val->suburb);
$data[‘postcode’] = str_replace(‘”‘,”,$val->postcode);
$data[‘state’] = str_replace(‘”‘,”,$val->state);
$data[‘dc’] = str_replace(‘”‘,”,$val->dc);
$data[‘type’] = str_replace(‘”‘,”,$val->type);
$data[‘lat’] = str_replace(‘”‘,”,$val->lat);
$data[‘lon’] = str_replace(‘”‘,”,$val->lon);
$id = $val->id;
// update the row again
$this->update_again($data,$id );

endforeach;

}

function update_again($data, $id){
$this->db->where(‘id’,$id)->update(‘tbl_states_details’,$data);
}

Advertisements

1 thought on “Import CSV data into to MySQL database”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s