Re: [R] r: RODBC QUESTION

From: Vitor Chagas <vitorchagas_at_yahoo.com>
Date: Sun 01 Jan 2006 - 06:30:53 EST


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

> 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@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

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 Received on Sun Jan 01 06:40:22 2006

This archive was generated by hypermail 2.1.8 : Fri 03 Mar 2006 - 03:41:45 EST