3

I need to read from a data base where one of the columns contains 'T' and 'F' values using RODBC sqlQuery function. However my results keep coming out with 'TRUE' and 'FALSE'. I found the following post for reading csv files. Is there a similar workaround for the sqlQuery function?

read.table reads "T" as TRUE and "F" as FALSE, how to avoid?

Edit : This is the code I'm running as requested in the comments. It won't be executable without access to the DB though ... 

conn = odbcConnect(dataBaseName)
results = sqlQuery(conn, paste0("select ID, andate, antime from table.e4 where ticker in (",paste(shQuote(IDvector, type = "sh"), collapse = ', '),");"))
close(conn)

ID is the field that contains 'T's and 'Fs

Community
  • 1
  • 1
hjw
  • 1,279
  • 1
  • 11
  • 25
  • 1
    Post a toy example for people to work with if you can. It always helps. – Simon O'Hanlon Oct 28 '13 at 11:39
  • I tend to go through the `DBI` package and this gets the correct types from my database. Not sure how much code you have, but you could try it as a test. As @SimonO101 said, some example code would help. – Sam Mason Oct 28 '13 at 11:43
  • 2
    Have you looked at the `as.is` option for `sqlQuery`? – JBecker Dec 12 '13 at 04:07
  • It worked! Thanks so much. If you post it as an answer I'll be more than happy to accept it – hjw Dec 13 '13 at 03:13

1 Answers1

2

sqlQuery function has as.is option, from the RODBC manual:

as.is which (if any) columns returned as character should be converted to another type? Allowed values are as for read.table. See ‘Details’.

Details
... if as.is is true for a column, it is returned as a character vector. Otherwise (where detected) date, datetime and timestamp values are converted to the "Date" or "POSIXct" class. ...

zx8754
  • 52,746
  • 12
  • 114
  • 209