Site icon R-bloggers

Dump MySQL to CSV using R

[This article was first published on Sustainable Research » Renglish, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Based on a related post on one of my favorite python-lists I remembered, that I wrote a similar snipplet some time ago.

So if you want to dump your whole MySQL database to csv-files you can recycle the following code:

?Download mysql2cvs.R
1
2
3
4
5
6
7
8
9
require(RMySQL)
m<-MySQL()
summary(m)
con<-dbConnect(m, dbname = "YOURDB", host="localhost", port=8889, user="YOURUSER", pass="YOURPASS", unix.sock="/Applications/MAMP/tmp/mysql/mysql.sock") # in case you are using MAMP 
tables<-dbListTables(con)
 
for (i in 1 : length(tables)){
temp<-(dbReadTable(con, tables[i]))
write.table(temp, tables[i], row.names=F)}

This also is a great way to use my new source-code plugin (WP-CodeBox)
Enjoy!

To leave a comment for the author, please follow the link and comment on their blog: Sustainable Research » Renglish.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.