Hello Allan,
You can work in two different ways, from Excel using
RExcel or from R with RODBC. Personally i prefer
working from R.
You can start by giving names to the excel ranges
(remember to put the var names in the 1st line), then
run the following code
to select the excel spreadsheet
library(RODBC)
# Select XLS File
xls.file = choose.files(filters = "*.xls")
workdir = unlist(strsplit(xls.file, "\\\\"))
workdir = paste(workdir[-length(workdir)],
collapse="/")
setwd(workdir)
# Connect to XLS Data
channel <- odbcConnectExcel(xls.file)
after this u can check what tables (ranges) are
available to work with
sqlTables(channel)
if you have a range named «Claims», use something like
this to load it in to R
xlClaims = sqlQuery(channel, paste("SELECT * FROM
Claims"))
and close the connection with
close(channel)
Hope it helps, and sorry for my poor english. Best
regards,
Vitor
--- Clark Allan <Allan at STATS.uct.ac.za> wrote:
> hello all
>
>
> i have a quick question. i have been using the RODBC
> library (trying to
> read Excel data
> into R but i am doing this by using Rexcel. this is
> probably not the
> correct forum -
> sorry for this).
>
> my code is shown below:
>
> Sub A()
>
> 'start the connection to R
> Call RInterface.StartRServer
>
> RInterface.RRun "library(RODBC)"
> RInterface.RRun "A =
> odbcConnectExcel('c:/TRY.xls')"
>
> RInterface.RRun "q1 = sqlFetch(A, 'Sheet1')"
>
> RInterface.RRun "odbcClose (A)"
>
> Worksheets("out").Activate
>
> Call RInterface.GetArray("q1", Range("A1"))
>
> Call RInterface.StopRServer
>
> End Sub
>
>
> i have included four examples below. on the left
> hand side we have the
> data as it appears
> in Excel and on the right hand side we have the
> output from the code
> (outputted to the
> 'out' sheet in excel). in the first example the
> code works while in the
> other three
> exampl0es it does not. ('a' is some character) when
> i use the commands
> in r directly everything works correctly (ie missing
> values are treated
> as NA - <characters is treated similarly>)
>
> can anyone show me how to solve this!
>
> ANOTHER QUESTION: am i allowed to have numeric and
> character values in
> the same column when importing from Excel to R?
> (seems like i cant)
>
> thanking you in advance!
>
> wishing you all a happy new year (in advance)
> /
> allan
>
>
> Y X1 X2 1 6 3
> 1 6 3 2 6 2
> 2 6 2 3 5 2
> 3 5 2
>
>
> Y X1 X2 0
> 1 6 3
> 2 6 2
> 3 a 2
>
>
> Y X1 X2 0
> 1 6 3
> 2 6 2
> 3 a 2
>
>
> Y X1 X2 0
> 1 3
> 2 6 2
> 3 5 2>
______________________________________________
> R-help at 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