#
console
Data, R

R & Access not R & Excel

Excel is the poor man’s database – avoid CSV files – even if you have a small amount of data there are database softwares out there that will greatly facilitate your work.

Using Microsoft Access and R on a basic level – single table – already allows cleaning a lot of the analysis process. Plus it is extremely easy.

I used to store everything on .csv files. loading files into R to modify/add rows – then re-write() the file … what a mess! Now I use Access with the RODBC package straight from the cran repository.

First I Open Microsoft Access and create a blank database with an empty table named “table” and save.

Now we can simply connect to the database.

install.packages("RODBC")
library(RODBC)

#Connect to Access db
connection <- odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:/[...]Blank DataBase.accdb")

Note that I need to use odbcDriverConnect because I’m on Windows x64 if you are on x32 you can probably use odbcConnect().

Once we are connected to the database we can fill the empty table we created.

sqlSave(connection, df, tablename = "table", append = TRUE, rownames=FALSE)

You might encounter an error if you try to append date formats POSIXlt and the like. The simplest and most inelegant way to fix this is to (after you have encountered the error) open that table in access and change the data type of the column that includes the date to … “Date”. Then re-run the sqlSave() function.

Finnally, how to fetch that data from that table.

Assuming you haven’t closed the connection simply:

table <- sqlFetch(connection, tablename="table")

#Close the connection
odbcClose(connection)

Now you can use Access on a very basic level – just store your data in tables rather than cvs file it is much easier to append, load, etc.

Advertisements

Discussion

Trackbacks/Pingbacks

  1. Pingback: R and Access – for loops | SocialFunction() - September 25, 2014

  2. Pingback: R and Access - for loops | operation CDL - September 29, 2014

  3. Pingback: R and Access - for loops - Jabber Cruncher - May 24, 2015

reply()

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: