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
Recent Comments