In this post, we will discuss How can I output MySQL query results in CSV files format which we can do using select into outfile statement before giving the name of the file along with the extension.
Files Extraction in CSV Format
We can get a csv format of the data stored in a database using a statement outfile so for the same we have given an example below to get to know how does it work and implement.
SELECT column1, column2, column3 INTO OUTFILE 'output.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM your_ table;
In this example, given above we are getting three columns (column1, column2, and column3) from a table called and outputting the results to a file called output.csv.
The field terminated by ‘,’ the clause specifies that the fields in the output should be separated by commas, and the enclosed by ‘ ” ‘clause specifies that each field should be enclosed in double- quotes. The LINES TERMINATED BY ‘\n’ clause specifies that each row should be terminated by a newline character.
Once you execute this query, the output file will be created in the location specified by the into file out clause. You can then open the file in a text editor or spreadsheet application to view the results in CSV format.
For the same, we can also use another method for getting the csv format using a MySQL client program such as MySQL or MySQLdump to run and get the result as a csv file, and for the same, we have given an example to execute:
mysql -u username -p -e "SELECT column1, column2, column3 FROM your_table" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > output.csv
In the above command, we have used the MySQL command line client to execute a query that make three columns from a table called Your. The output is piped to the sed command, which replaces tabs with commas and adds double quotes around each field. The resulting CSV data is then redirected to a file called Output .csv.
To learn more about How can I output MySQL query results in CSV format visit: by solution center
To learn more about MySQL tutorials and the solutions to the problems along with concepts and tutorials of solutions list and learn visit: MySQL Tutorials And Problems.
