Export data in CSV format using SQL query – MySql database
We have been working for a solution and like every other solution it also has some reporting stuff. As you can not solve each and every reporting need of customer, you also provide some data formats in exports so that customers can do whatever reporting with their data.
In case of large data, export can take longer time and also in some cases when you need very specific data but large number of records, it is better to query you DB directly for the result rather than exporting complete data.
For MySql database, there are administrative tools like phpMyAdmin and can be used easily for this kind of requirements. But if you do not have phpMyAdmin installed over your database and have server access using SSH only, you can use mysql client to execute your queries to get the results.
1. Store result of query in File: You can run mysql client utility for executing the SQL query on command line and redirect the output in any file.
#mysql -e "SQL statement" -u username -ppassword > filename
2. Specify outfile in SQL query: You can specify outfile while running the SQL query,
SELECT Id,Name,Email FROM accounts INTO OUTFILE 'data.txt';
This file is generally generated in datadir of mysql configured in my.cnf file. There are some versions of MySql which gives error while specifying path of out file.
SELECT Id,Name,Email FROM accounts INTO OUTFILE '/data/backup/data.txt';
Gives following error:
ERROR 1 (HY000): Can’t create/write to file ‘/data/backup/data.txt’ (Errcode: 13), This error can also occur if you do not have proper rights of writing results to out file.
3. Generating CSV file using SQL query: the default file generated is Tab separated file and can be used as data file, but CSV is more popular format so by using following query CSV file can be generated directly.
SELECT Id,name,email,balance INTO OUTFILE 'data.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM accounts;
Most Commented Posts
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
Comments
No comments yet.
Leave a comment