Generate CSV file using MYSQL command

Hello Guys! This is my first blog post of this month and the topic is “Generate CSV file using MYSQL command” In this post I will discuss about an interesting feature of MYSQL. MySQL provides an easy way for generating csv files by writing the output of a select statement into a plain text file. We can use the INTO OUTFILE syntax to generate a text file.We can also specify the character which will be used to separate the fields and also the character that will be used to terminate a line.We can then simply import that csv file in any application that accepts CSV format.

In the MYSQL console type:-

SELECT * FROM table_name INTO OUTFILE '/folder/filename.csv';

The above query will return set of rows, which can be saved into a file for e.g /tmp/samplefile.csv.

We can also specify the FIELDS TERMINATED BY and LINES TERMINATED BY clause like this:-

SELECT * FROM subjects
INTO OUTFILE ‘/tmp/subjects.csv’
FIELDS TERMINATED BY ‘;’
LINES TERMINATED BY ‘n’

In the above query,the fields will be separated by semicolon, and each line will be terminated by a newline (n) character.

The screenshots are given below:-

SQL Query

Generated CSV file