I have an API that returns
str(test)
'data.frame': 35 obs. of 2 variables:
$ date : Date, format: "2017-05-23" "2017-05-24" "2017-05-25" "2017-05-26" ...
$ PX_LAST: num 52.3 52.1 49.8 50.6 50.5 ...
However, on closer look at the internal storage of the date variable... the dates are stored with an "L" appended to the end.
dput(test)
structure(list(date = structure(c(17309L, 17310L, 17311L, 17312L,
17316L, 17317L, 17318L, 17319L, 17322L, 17323L, 17324L, 17325L,
17326L, 17329L, 17330L, 17331L, 17332L, 17333L, 17336L, 17337L,
17338L, 17339L, 17340L, 17343L, 17344L, 17345L, 17346L, 17347L,
17350L, 17352L, 17353L, 17354L, 17357L, 17358L, 17359L), class = "Date"),
PX_LAST = c(52.3, 52.09, 49.76, 50.59, 50.48, 49.12, 49.22,
48.51, 48.22, 48.88, 46.87, 46.85, 46.97, 47.15, 47.45, 45.82,
45.67, 45.94, 45.46, 44.58, 43.51, 43.74, 44.08, 44.4, 45.31,
45.81, 46.02, 47.05, 48.01, 46.1, 46.4, 45.07, 45.32, 45.92,
46.64)), class = "data.frame", .Names = c("date", "PX_LAST"
), row.names = c(NA, 35L))
Is there anyway to change the way the date is stored to get r=rid of the L at the end? That extra L is causing an error when I try to write the data to sql database.
UPDATE
Thanks for the comments, rich, d.b. and Marius. Here is the SQL code that i'm using to write to the database.
OK in the spirit of trying to replicate this very confusing problem. I've done this. Here's the structure of one line of the data table that yields the restricted data type problem:
> oneLine <- flatFrame[1, 1-4]
> str(oneLine)
'data.frame': 1 obs. of 4 variables:
$ Ticker : Factor w/ 1 level "CLU7 Comdty": 1
$ date : Date, format: "2017-05-18"
$ VOLUME : num 44674
$ OPEN_INT: int 188049
Then I try to write this one line to the database in a new table and I get the attribute violation error.
dbWriteTable(con, "new7", oneLine, verbose=TRUE, overwrite=TRUE)
Error in result_insert_dataframe(rs@ptr, values) :
nanodbc/nanodbc.cpp:1791: 07006: [Microsoft][ODBC Driver 13 for SQL
Server]Restricted data type attribute violation
So now I try to clone the data frame:
rep_data <- data.frame(Ticker=as.factor("CLU7 Comdty"), date = as.Date("2017-05-18"), VOLUME=44674, OPEN_INT =as.integer(188049))
> str(rep_data)
'data.frame': 1 obs. of 4 variables:
$ Ticker : Factor w/ 1 level "CLU7 Comdty": 1
$ date : Date, format: "2017-05-18"
$ VOLUME : num 44674
$ OPEN_INT: int 188049
Exactly the same....But this write function produces no error.
dbWriteTable(con, "new8", rep_data, verbose=TRUE, overwrite=TRUE)
What is going on? Is there some phantom attribute in the data table that I'm not seeing?
Someone on github suggested that I use the dput() command to look at the internal structure of the data.
dput(oneLine)
structure(list(Ticker = structure(1L, .Label = "CLU7 Comdty", class = "factor"),
date = structure(17304L, class = "Date"), VOLUME = 44674,
OPEN_INT = 188049L), .Names = c("Ticker", "date", "VOLUME",
"OPEN_INT"), row.names = 1L, class = "data.frame")
dput(rep_data)
structure(list(Ticker = structure(1L, .Label = "CLU7 Comdty", class = "factor"),
date = structure(17304, class = "Date"), VOLUME = 44674,
OPEN_INT = 188049L), .Names = c("Ticker", "date", "VOLUME",
"OPEN_INT"), row.names = c(NA, -1L), class = "data.frame")
The salient difference in the date structure is that in the one that fails, oneLine, the internally stored date 17304L has an "L" appended to it. The replicated data set does not.