Generate CSV file with Header from MySQL Command Console

By Posted on

Generating a CSV file contains several records from MySQL Database using MySQL command console is something that can be done.Especially if we want to specifically define the header of each fields retrieved from each fields of MySQL specific table.

Below is the step which has to be followed in order to generate CSV file needed :

  1. Login to MySQL command console from the bash prompt as shown below :

mysql -uusername -p

The output will be displayed most likely as follows depends on your environment : 

[usernamed@hostname ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 135730
Server version: 5.6.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
  1. Execute the following command with the pattern shown below :
(SELECT 'Field 1','Field 2','Field 3','Field-n') UNION (select field_name_1,field_name_2,field_name_3,field_name_n from table_name INTO OUTFILE '/path_for_dumping_the_file/file_name.csv' FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\r\n');

Field 1, Field 2, Field 3, Field-n : It is the custom header or description which is placed in the top of the header field of each field retrieved from the selected table.

field_name_1, field_name_2, field_name_3, field_name_n : The name of the real field in the selected table.

table_name : The name of the selected table.

/path_for_dumping_the_file/file_name.csv : The path directory where the CSV format dump file will be generated with the path of /path_for_dumping_the_file/ and the name of file_name.csv. Change it accordingly to the environment. For an example : /tmp/generated-file-2016.csv

It will then generate a file in the specific path directory specified in the query executed. The file will have a header and each field will be separated by ‘;’ and the data in each field will be enclosed by ‘”‘. Every line will be terminated with a \r\n.

Execute the command pattern above in MySQL command console and adjust it with the condition and terms of your environment as shown below : 

mysql> (SELECT 'Field 1','Field 2','Field 3','Field-n') UNION (select field_name_1,field_name_2,field_name_3,field_name_n from table_name INTO OUTFILE '/path_for_dumping_the_file/file_name.csv' FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\r\n');

3. Open the file with Office Spreadsheet like Microsoft Office Excel, Libre Office Calc or any other program made solely for viewing or editing CSV file.

The generated process of CSV file has already finished.

Leave a Reply