---BeginMessage---
Hi, Jin,
---
Ricardo Rodríguez
Your XEN ICT Team
Xiaodong Jin[EMAIL PROTECTED] 09/11/06 3:10
I just need to query ordinary 3-column excel data e.g.
V1 V2 V3
I1 C1 1
I1 C1 1
I1 C1 1
I need to get select distinct V1, V3, count(distinct V2) as
CNT from TABLENAM group by 1,2 order by 1,2
V1 V3 CNT
I1 1 2
I1 3 2
I1 9 1
What do I need besides the package RMySQL?
Thanks
If you need/want to keep your records in Excel files, you don't need RMySQL at
all! It is only needed if you are going to store the records in a MySQL
relational database management system. From my point of view, this is the best
alternative as it will allow you to use the power of the RDBMS to design ad hoc
queries and retrieve just what you want out of the whole dataset.
Thus, sticking with Excel, there are two options as far as I know: RODBC or the
read.xls function included with a number of packages. read.xls temporarely
transform your xls files into csv ones. I've never successfully imported data
by using this path as I getting a number of errors likely related to my Perl
installation (the function uses Perl for the transformation to csv). RODBC is
out of the question here as far as we mostly use Mac and Linux boxes. Even
though there is ODBC for Mac OS X, we do prefer to avoid it and to natively get
the data from MySQL by using RMySQL.
As an alternative, you can manually transform the xls files to csv, read it by
using read.csv and then using SQL like manipulations on data frames (see this
for further reference http://tolstoy.newcastle.edu.au/R/help/06/05/26796.html)
It is quite easy to load csv files into a data frame, but as far as I know, you
can not use SQL sentences to subset it.
Here a rather simple example about loading csv files in a data frame by using
the data you have sent to me...
jin - read.csv(http://nvx.environmentalchange.net/@rrodriguez/R/jin.csv,sep
= ,)
print(jin)
Once again, I can not post to the list from this location. It will be much
better to keep the communication by the list. I am just a R newcomer. There are
many people out there mastering R and ready to help! I'll resend this message
to the list as soon as I can.
HTH,
Ricardo
---End Message---
__
R-help@stat.math.ethz.ch mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
