R / Excel : leading zeroes

Question

I am trying to read a worksheet in an Excel 2010 workbook file into R using both the xlsx and XLConnect packages. Both are dropping leading zeroes on zip code columns despite formatting the cells in the worksheet as 'Text'.

wb <- loadWorkbook('c:/~/file1.xlsx')
sheetNames <- getSheets(wb)

for(i in 1:length(sheetNames)){  # i = 2
    #dat1 <- read.xlsx('c:/~/file1.xlsx', sheetNames[i], as.data.frame = T)
    dat1 <-  readWorksheetFromFile('c:/~/file1.xlsx', sheetNames[i])
}

Does anyone have suggestions for how to deal with this?

Try specifying the argument `colTypes`. See `?readWorksheet`. — Andrie, Jun 04 '14 at 16:54
@Andrie : Thanks, adding 'colType = "character"' did the job. — screechOwl, Jun 04 '14 at 17:37
@Andrie Can you please post your response as an answer so screechOwl can accept it. — Rohit Das, Jun 04 '14 at 18:22

score 1 · Answer 1 · edited Jan 19 '21 at 15:49

1

You can also fix this afterwards with sprintf:

# some example data
x <- c(225,4867,52,15732,9514,78142)
# getting the leading zeros back
x <- sprintf("%05s", x)

> x
[1] "00225" "04867" "00052" "15732" "09514" "78142"

edited Jan 19 '21 at 15:49

ulfelder

5,148
1
20
37

answered Jun 04 '14 at 18:20

Jaap

77,147
31
174
185

score 1 · Accepted Answer · answered Jun 05 '14 at 08:46

1

You can specify the column types as an argument to readWorksheet() or readWorksheetFromFile(), by using the argument colType = ....

For example, to read all columns as character, use:

readWorkSheet(..., colType="character")

answered Jun 05 '14 at 08:46

Andrie

170,733
42
434
486

R / Excel : leading zeroes

2 Answers2

Linked