Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » Export data to CSV file

Export data to CSV file

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

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

Populate the table:

INSERT INTO emp VALUES 
  (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);

Export table to CSV file:

SELECT * FROM emp
  INTO OUTFILE 'emp_exp.csv'
  FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY 'rn';

Result:

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.