r: RODBC QUESTION

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

r: RODBC QUESTION

Clark Allan-2
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
______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
Reply | Threaded
Open this post in threaded view
|

Re: r: RODBC QUESTION

Vitor Chagas
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 <[hidden email]> 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>
______________________________________________
> [hidden email] mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide!
http://www.R-project.org/posting-guide.html

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
Reply | Threaded
Open this post in threaded view
|

Re: r: RODBC QUESTION

Roger J. Bos
Clark,

I agree with Vitor that working in R might be easier, but it seems that you
are working in the excel VBA environment and there may be good reason why
you want to do so that we don't know about.  If so, why use Rexcel function
to read the file into excel when you can use VBA code to open the file in
excel and then you can send the data you need to analyse to R using Rexcel?

And of course the great thing about VBA is that if you don't know how to
code what you want to do, you can always record it as a macro and then view
the code (a neat feature that S-PLUS has too).
Good luck and please follow up with more questions if our suggestions are of
no help to you.

Thanks,

Roger



On 12/31/05, Vitor Chagas <[hidden email]> wrote:

>
> 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 <[hidden email]> 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>
> ______________________________________________
> > [hidden email] mailing list
> > https://stat.ethz.ch/mailman/listinfo/r-help
> > PLEASE do read the posting guide!
> http://www.R-project.org/posting-guide.html
>
> ______________________________________________
> [hidden email] mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide!
> http://www.R-project.org/posting-guide.html
>
        [[alternative HTML version deleted]]


______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html