Thanks,
Roger
library(RODBC)
library(fPortfolio)
Loading required package: timeSeries
Attaching package: ‘timeSeries’
The following object is masked from ‘package:zoo’:
time<-
Loading required package: fBasics
Rmetrics Package fBasics
Analysing Markets and calculating Basic Statistics
Copyright (C) 2005-2014 Rmetrics Association Zurich
Educational Software for Financial Engineering and Computational Science
Rmetrics is free software and comes with ABSOLUTELY NO WARRANTY.
https://www.rmetrics.org --- Mail to: i...@rmetrics.org
Attaching package: ‘fBasics’
The following object is masked from ‘package:TTR’:
volatility
Loading required package: fAssets
Error in loadNamespace(j <- i[[1L]], c(lib.loc, .libPaths()),
versionCheck = vI[[j]]) :
there is no package called ‘DEoptimR’
Error: package ‘fAssets’ could not be loaded
source(.trPaths[5], echo=TRUE, max.deparse.length=150)
library(RODBC)
library(fPortfolio)
Loading required package: fAssets
Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck =
vI[[i]]) :
there is no package called ‘sn’
Error: package ‘fAssets’ could not be loaded
library(RODBC)
library(fPortfolio)
Loading required package: fAssets
Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck =
vI[[i]]) :
there is no package called ‘sn’
Error: package ‘fAssets’ could not be loaded
library(timeSeries)
head(SWX.RET$SBI)
Error in head(SWX.RET$SBI) :
error in evaluating the argument 'x' in selecting a method for
function 'head': Error: object 'SWX.RET' not found
library(fPortfolio)
Loading required package: fAssets
Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck =
vI[[i]]) :
there is no package called ‘sn’
Error: package ‘fAssets’ could not be loaded
library(fPortfolio)
Loading required package: fAssets
Rmetrics Package fAssets
Analysing and Modeling Financial Assets
Copyright (C) 2005-2014 Rmetrics Association Zurich
Educational Software for Financial Engineering and Computational Science
Rmetrics is free software and comes with ABSOLUTELY NO WARRANTY.
https://www.rmetrics.org --- Mail to: i...@rmetrics.org
Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck =
vI[[i]]) :
there is no package called ‘slam’
Error: package or namespace load failed for ‘fPortfolio’
library(fPortfolio)
Package Rsolnp (1.14) loaded. To cite, see citation("Rsolnp")
Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck =
vI[[i]]) :
there is no package called ‘kernlab’
Error: package or namespace load failed for ‘fPortfolio’
library(fPortfolio)
Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck =
vI[[i]]) :
there is no package called ‘rneos’
Error: package or namespace load failed for ‘fPortfolio’
library(fPortfolio)
Rmetrics Package fPortfolio
Portfolio Optimization
Copyright (C) 2005-2014 Rmetrics Association Zurich
Educational Software for Financial Engineering and Computational Science
Rmetrics is free software and comes with ABSOLUTELY NO WARRANTY.
https://www.rmetrics.org --- Mail to: i...@rmetrics.org
head(SWX.RET$SBI)
[1] -0.0020881194 -0.0001045205 -0.0013597617 0.0004185852
0.0000000000 -0.0010467917
str(SWX.RET$SBI)
num [1:1916] -0.002088 -0.000105 -0.00136 0.000419 0 ...
source(.trPaths[5], echo=TRUE, max.deparse.length=150)
mydata<-as.timeSeries(SWX.RET)
head(mydata)
GMT
SBI SPI SII
LP25 LP40 LP60
2000-01-04 -0.0020881194 -0.034390059 0.00001367381 -0.011994298
-0.018013035 -0.026155259
2000-01-05 -0.0001045205 -0.010408271 -0.00495530624 -0.003657054
-0.005837489 -0.009011403
2000-01-06 -0.0013597617 0.012119128 0.00381289851 -0.001323897
-0.001644737 -0.002395959
2000-01-07 0.0004185852 0.022461656 -0.00061621046 0.007714991
0.011660151 0.017062613
2000-01-10 0.0000000000 0.002107677 0.00238057889 0.003029081
0.004565523 0.006948020
2000-01-11 -0.0010467917 -0.002773654 -0.00029384531 -0.002422531
-0.003142903 -0.004183466
source(.trPaths[5], echo=TRUE, max.deparse.length=150)
df2beSavedByRODBC =as.data.frame(mydata)
str(df2beSavedByRODBC)
'data.frame': 1916 obs. of 6 variables:
$ SBI : num -0.002088 -0.000105 -0.00136 0.000419 0 ...
$ SPI : num -0.03439 -0.01041 0.01212 0.02246 0.00211 ...
$ SII : num 0.0000137 -0.0049553 0.0038129 -0.0006162 0.0023806 ...
$ LP25: num -0.01199 -0.00366 -0.00132 0.00771 0.00303 ...
$ LP40: num -0.01801 -0.00584 -0.00164 0.01166 0.00457 ...
$ LP60: num -0.02616 -0.00901 -0.0024 0.01706 0.00695 ...
sqlSave(xf,
dat=df2beSavedByRODBC,tablename="testTable",rownames=TRUE,append=FALSE,addPK=FALSE,verbose=FALSE)
sqlString = "select * from testTable"
dataFrameFromDB = sqlQuery(xf, sqlString,errors=TRUE);
str(dataFrameFromDB)
'data.frame': 1916 obs. of 7 variables:
$ rownames: chr "2000-01-04" "2000-01-05" "2000-01-06" "2000-01-07"
...
$ SBI : num -0.002088 -0.000105 -0.00136 0.000419 0 ...
$ SPI : num -0.03439 -0.01041 0.01212 0.02246 0.00211 ...
$ SII : num 0.0000137 -0.0049553 0.0038129 -0.0006162 0.0023806
...
$ LP25 : num -0.01199 -0.00366 -0.00132 0.00771 0.00303 ...
$ LP40 : num -0.01801 -0.00584 -0.00164 0.01166 0.00457 ...
$ LP60 : num -0.02616 -0.00901 -0.0024 0.01706 0.00695 ...
***************************************************************
This message and any attachments are for the named person's use only.
This message may contain confidential, proprietary or legally privileged
information. No right to confidential or privileged treatment
of this message is waived or lost by an error in transmission.
If you have received this message in error, please immediately
notify the sender by e-mail, delete the message, any attachments and all
copies from your system and destroy any hard copies. You must
not, directly or indirectly, use, disclose, distribute,
print or copy any part of this message or any attachments if you are not
the intended recipient.
-----Original Message-----
From: r-help-boun...@r-project.org
[mailto:r-help-boun...@r-project.org] On Behalf Of Andrew
Sent: Friday, October 03, 2014 1:40 PM
To: r-help@r-project.org
Subject: [R] Workaround for RODBC asymmetric numeric data treatment
Note: I did raise report the issue below to r-sig...@r-project.org,
but didn't see any reply.
I'm hoping somebody on r-help can help me devise a workaround for a
problem I'm having with RODB:
I use RODBC to read and write a good deal of data to SQL Server and
I'd be extremely grateful
if anyone has found a workaround in order to be able to write
dataframes to SQL Server
using RODBC dynamically created SQL tables and read the data from
those tables, or indeed any
arbitrary SQL Server table with "float" datatypes and end up with
numeric columns instead of "factor" columns
in a dataframe in R.
I have found that when RODBC creates a Microsoft SQL Server data table
from a dataFrame using sqlSave(....append=FALSE),
RODBC uses the SQL "float" datatype to store R numeric data in a
dynamically-created table on the server.
However, when RODBC reads any SQL Server "float" datatype from SQL
Server via sqlQuery it interprets float columns as "factor" data.
I created a standalone sample below to demonstrate the odd behavior of
RODBC that I hope to overcome:
# Assuming the reader has access to SQL Server the code below is
self-contained and repeatable
# I believe it demonstrates unexpected and undesirable behavior in RODBC
library(RODBC)
library(fPortfolio)
library(timeSeries)
head(SWX.RET$SBI)
str(SWX.RET$SBI)
mydata<-as.timeSeries(SWX.RET)
head(mydata)
df2beSavedByRODBC =as.data.frame(mydata)
str(df2beSavedByRODBC)
# shows the numeric data in the dataframe
#
# data.frame': 1916 obs. of 6 variables:
# $ SBI : num -0.002088 -0.000105 -0.00136 0.000419 0 ...
# $ SPI : num -0.03439 -0.01041 0.01212 0.02246 0.00211 ...
...
# Let's save the dataframe to SQL Server:
dbconn<-odbcDriverConnect(connection="Driver={SQL
Server};server=_YOURSERVERNAMEHER_;database=_YOURDBNAME_;Trusted_Connection=True;");
sqlSave(channel=dbconn,dat=df2beSavedByRODBC,tablename="testTable",rownames=TRUE,append=FALSE,addPK=FALSE,verbose=FALSE)
# The sqlSave above works very well. The new table is create in the
Microsoft SQL database and the ddl for the table is:
#
# [dbo].[testTable](
# [rownames] [varchar](255) NULL,
# [SBI] [float] NULL,
# [SPI] [float] NULL,
# [SII] [float] NULL,
# [LP25] [float] NULL,
# [LP40] [float] NULL,
# [LP60] [float] NULL
# )
# The numeric values from the dataframe are stored as float (i.e.
numeric) in SQL server -- good!
## now let's read back the data RODBC stored in SQL server from a SQL
table RODBC created:
sqlString = "select * from testTable"
dataFrameFromDB = sqlQuery(dbconn,sqlString,errors=TRUE);
str(dataFrameFromDB)
#
# 'data.frame': 1916 obs. of 7 variables:
# $ rownames: Factor w/ 1916 levels "2000-01-04","2000-01-05",..: 1 2
3 4 5 6 7 8 9 10 ...
# $ SBI : Factor w/ 1742 levels "-0.00041080415489958",..: 349 42
161 1418 828 48 49 1419 1024 135 ...
# $ SPI : Factor w/ 1848 levels "-0.0020169904194276",..: 445 48
970 883 1187 377 1157 1065 951 1840 ...
...
#********* RODBC wrote numeric data to SQL Server as float, but read
the same data back as Factor ! ********
I could use some help to create a robust and flexible workaround for
RODBC's asymmetric treatment of numeric data.
If there were some way to force RODBC sqlQuery to interpret all SQL
Server float datatypes as numeric my problem would be solved.
FWIW: RODBC does interpret the SQL Server "real" datatype as numeric.
Thank you,
Andrew
[[alternative HTML version deleted]]
______________________________________________
R-help@r-project.org 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.
______________________________________________
R-help@r-project.org 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.
