Keeping leading zeros using RODBC

Question

I am trying to import data from an Access database into R. I would like to import the CIP codes for some majors, which can contain leading zeros. RODBC is converting the CIP code to a numeric value even though it is defined as text in Access. Can anyone shed light on how I can coerce this field into a text field on import?

score 7 · Accepted Answer · answered Jul 12 '12 at 13:28

7

Try using the argument as.is = TRUE in the sqlQuery function. That usually does the trick for me.

answered Jul 12 '12 at 13:28

BenBarnes

19,114
6
56
74

2

That almost does the trick. I tried that and it then made all of the columns into character types, including the dates and factors. I did the following instead (the CIP codes are the first 4 fields) and that did the trick. `df <- sqlFetch( conn, "tbl", as.is=c(rep(TRUE,times=4),rep(FALSE,times=32)))` – dmonder Jul 12 '12 at 13:37

score 1 · Answer 2 · answered Jul 12 '12 at 13:33

1

library(RODBC)
channel=odbcConnectAccess2007("plz.accdb")
plz = sqlQuery(channel,"SELECT * from PLZ",as.is=TRUE)

answered Jul 12 '12 at 13:33

Dieter Menne

10,076
44
67

Keeping leading zeros using RODBC

2 Answers2