Saturday, April 7, 2012

Import csv file to MySql Database


Recently looking for ways to Import data from csv file to MySql database. Mysql  database has powerful feature that allows user to import csv file directly to database. Sample code shows below:

create table statement:
DROP TABLE IF EXISTS `share_market`.`klse`;
CREATE TABLE  `share_market`.`klse` (
  `dates` date DEFAULT NULL,
  `opens` decimal(10,4) DEFAULT '0.0000',
  `highs` decimal(10,4) DEFAULT '0.0000',
  `lows` decimal(10,4) DEFAULT '0.0000',
  `closes` decimal(10,4) DEFAULT '0.0000',
  `volumn` decimal(20,4) DEFAULT '0.0000'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


command to import csv file:
LOAD DATA LOCAL INFILE 'd:\klci analysis.csv'
into table klse
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(dates, opens, highs,lows,closes, volumn);

where table name is "klse", field name is "dates", "opens", "highs","lows","closes" and  "volumn"
Use "s" for each field to avoid reserve word "open" and "close"

No comments: