[From nobody Thu Nov  9 13:52:38 2006
Date: Thu, 09 Nov 2006 13:45:21 +0100
From: "=?ISO-8859-1?Q?Ricardo=20Rodr=EDguez?=" <webmaster@xen.net>
To: <close2ceo@yahoo.com>
Subject: Re: [R] query in R
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi, Jin,

---
Ricardo Rodr=EDguez
Your XEN ICT Team
>>> Xiaodong Jin<close2ceo@yahoo.com> 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
>  =20
>  I need to get "select distinct V1, V3, count(distinct V2) as=20
>CNT from TABLENAM group by 1,2 order by 1,2"
>  V1 V3 CNT
>  I1 1 2=20
>  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 transformat=
ion 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/0=
5/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 =3D ",")
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
]