Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » Import, change and format data from CSV file

Import, change and format data from CSV file

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

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)
);

We have next CSV-file 'emp2.csv' with custom formatted date ('DD-MM-YYYY'):
"empno";"ename";"job";"mgr";"hiredate";"sal";"comm";"deptno"
7369;"SMITH";"CLERK";7902;17-12-1980;800,0;null;20
7499;"ALLEN";"SALESMAN";7698;20-02-1981;1600,0;300,0;30
7521;"WARD";"SALESMAN";7698;22-02-1981;1250,0;500,0;30
7566;"JONES";"MANAGER";7839;02-04-1981;2975,0;null;20
7654;"MARTIN";"SALESMAN";7698;28-09-1981;1250,0;1400,0;30

 

And suppose, we have to increase a salary (for example: salary = salary * 2:

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)
);

 

Check result:

SELECT * FROM emp;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 | 1600.00 |    0.00 |     20 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 3200.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 2500.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 5950.00 |    0.00 |     20 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 2500.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.