Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » Import data from CSV file

Import data from CSV file

Author: rootsql Date: 15-11-2011, 21:46

Table definition:

CREATE TABLE emp(
  empno INT(11) NOT NULL,
  ename VARCHAR(10) DEFAULT NULL,
  job VARCHAR(9) DEFAULT NULL,
  mgr DECIMAL(4, 0) DEFAULT NULL,
  hiredate DATE DEFAULT NULL,
  sal DECIMAL(7, 2) DEFAULT NULL,
  comm DECIMAL(7, 2) DEFAULT NULL,
  deptno INT(11) DEFAULT NULL,
  PRIMARY KEY (empno)
);

CSV-file 'emp.csv':

"empno";"ename";"job";"mgr";"hiredate";"sal";"comm";"deptno"
7369;"SMITH";"CLERK";7902;1980.12.17;800,0;null;20
7499;"ALLEN";"SALESMAN";7698;1981.02.20;1600,0;300,0;30
7521;"WARD";"SALESMAN";7698;1981.02.22;1250,0;500,0;30
7566;"JONES";"MANAGER";7839;1981.04.02;2975,0;null;20
7654;"MARTIN";"SALESMAN";7698;1981.09.28;1250,0;1400,0;30

Now, we are going to import data from 'data.csv' to 'mytable':

LOAD DATA INFILE 'emp.csv'
  INTO TABLE emp
  FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY 'rn'
  IGNORE 1 LINES;

'IGNORE 1 LINES' is used to skip table header in CSV-file. 

 

Check result:

SELECT * FROM emp;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800.00 |    0.00 |     20 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975.00 |    0.00 |     20 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
Dear visitor, you went to the site as unregistered user.
We recommend you to Register or Sign In.

Comments:

Leave a comment
Information
Members of Guests cannot leave comments.