Export amazon mysql database to an excel sheet

Question

I have an ec2-instance on which mysql database is there and now there are multiple tables have huge values which i want to export into an excel sheet into my local system or even some place at S3 will also work , how can i achieve this ?

Possible duplicate of [Mysqldump in CSV format](https://stackoverflow.com/questions/12040816/mysqldump-in-csv-format) — Jacob, Dec 27 '18 at 06:18

Tim Biegeleisen · Answer 1 · 2018-12-27T06:44:40.090

2

Given that you installed your own MySQL instance on an EC2 node, you should have full access to MySQL's abilities. I don't see any reason why you can't just do a SELECT ... INTO OUTFILE here:

SELECT *
FROM yourTable
INTO OUTFILE 'output.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Once you have the CSV file, you may transfer it to a box running Excel, and use the Excel import wizard to bring in the data.

Edit:

Based on your comments below, it might be the case that you need to carefully select an output path and location to which MySQL and your user have permissions to write.

edited Dec 27 '18 at 06:44

answered Dec 27 '18 at 06:20

Tim Biegeleisen

502,043
27
286
360

Not working , it is saying Access denied for user ''@'%' (using password: YES) – Vatsal Rahul Dec 27 '18 at 06:36
If you can't even connect to your MySQL instance on EC2, then you have an entirely different problem. And this has nothing to do with how to export your data from MySQL. If you can't connect to MySQL, basically you won't be able to do anything. – Tim Biegeleisen Dec 27 '18 at 06:38
But i am able to login in to my instance and access my database and view all my tables – Vatsal Rahul Dec 27 '18 at 06:43
Then maybe the location is the problem. See my updated answer. – Tim Biegeleisen Dec 27 '18 at 06:44
does output.csv should already be existing or it will automatically create one – Vatsal Rahul Dec 27 '18 at 06:46
It should automatically create one AFAIK, assuming you can get around these permissions problems. – Tim Biegeleisen Dec 27 '18 at 06:46
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/185810/discussion-between-vatsal-rahul-and-tim-biegeleisen). – Vatsal Rahul Dec 27 '18 at 06:47

score 1 · Accepted Answer · answered Jun 30 '20 at 23:28

Another way to export CSV files from RDS Mysql and without getting Access denied for user '<databasename>'@'%' (using password: YES) is doing the following command:

mysql -u username -p --database=dbname --host=rdshostname --port=rdsport --batch -e "select * from yourtable" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > yourlocalfilename.csv

The secret is in this part: --batch -e "select * from yourtable" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > yourlocalfilename.csv

Export amazon mysql database to an excel sheet

2 Answers2