Wednesday 2 September 2015

How to export mysql query results to csv?

How to export mysql query results to csv?

To Learn OR understand the exporting mysql results in CSV.
Lets have below simple example.


  1. Step 1: Create a employee table
    CREATE TABLE IF NOT EXISTS `employee` (
      `id` int(11) unsigned NOT NULL,
      `first_name` varchar(100) DEFAULT NULL,
      `last_name` varchar(100) DEFAULT NULL,
      `gender` enum('m','f') DEFAULT NULL,
      `status` enum('0','1') NOT NULL COMMENT '0-Inactive, 1-Active',
      `address` varchar(255) DEFAULT NULL,
      `created_at` datetime DEFAULT NULL,
      `modified_at` datetime DEFAULT NULL
    ) ENGINE=InnoDB AUTO_INCREMENT=6;

  2. Add The records in employee table
    INSERT INTO `employee` (`id`, `first_name`, `last_name`, `gender`, `status`, `address`, `created_at`, `modified_at`) VALUES
    (1, 'Sunil', 'Malhotra', 'm', '1', '3020 new town, chandigarh', '2015-09-02 12:38:00', '2015-09-02 12:38:00'),
    (2, 'Anil ', 'Yadav', 'm', '1', '3020 new town, chandigarh', '2015-09-02 12:38:00', '2015-09-02 12:38:00'),
    (3, 'Aman ', 'Verma', 'm', '1', '3020 new town, chandigarh', '2015-09-02 12:38:00', '2015-09-02 12:38:00'),
    (4, 'Ram ', 'Singh', 'm', '1', '3020 new town, chandigarh', '2015-09-02 12:38:00', '2015-09-02 12:38:00'),
    (5, 'Arun', 'Kumar', 'm', '1', '3020 new town, chandigarh', '2015-09-02 12:38:00', '2015-09-02 12:38:00');
    

  3. Create a folder where you are going to export the csv file.
  4. Execute the Mysql Query.
    SELECT id,first_name,last_name,gender, STATUS, address FROM employee
    INTO OUTFILE 'E:/wamp/www/export/employee3.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    LINES TERMINATED BY '\n';