How To Import RxSqlServerData Data Class with Datetimes Already POSIXct? RRS feed

  • Question

  • I am trying to Import a SQL Server Data table into R Server. The data table in SQL Server has datetimes formatted as datetime, but when I use the following code the tables do not come into R in appropriate format.

    xxx<- RxSqlServerData(table = "vwTCMSDataGPC",
                                        connectionString = connStr,
                                        colClasses = col)

    I can use rxGetVarInfo(xxx) to show that the table imports the datetime files as character classes. This means I have to run this code to get my dates in appropriate format

    xxx<- rxDataStep(inData = xxx,
                        transforms = list(BeginTime = as.POSIXct(BeginTime),
                                          RestoreTime = as.POSIXct(RestoreTime),
                                          ERTGeneratedTime = as.POSIXct(ERTGeneratedTime)),
                        maxRowsByCols = 1000000000)

    Due to the size of my data this take 30-40 minutes. Thats 10% of my workday gone each time I have to run this. I would like to be able to simply put BeginTime = "POSIXct" etc in my object named col used in the RxSqlServerData step, but that doesn't work and you get an error that suggests converting to POSIXct the way I did.

    I want it to simply import my sql server data as an object with appropriate formats when I run rxGetVarInfo that way I do not have to spend 10% of my day formatting a file. Also, this is important for when this solution gets put into production. There is not time to do this tedious step.

    How do I make this work how I would like it to work?

    Friday, May 19, 2017 12:21 PM