How can I import all the decimal places in a mixed

Question

I am trying to import many Excel spreadsheets into R using XLConnect. Two columns interest me, one containing variable names, and the other containing values. These values vary between being characters or numeric. The authors of the spreadsheet have set the numeric values to show varied numbers of decimals depending on the cell, although I need all numeric values with all decimal places. However, because the column contains both characters and numbers, readWorksheet converts everything to characters, and therefore seems to only read the visible decimal places displayed in Excel inside the cells.

How can I import a column specifying that I want both the character fields and the printed decimals to their full inputed values (as a character vector)?

Apologies for the lack of MWE, due to the requirement of a spreadsheet.

Answer 1

From my interpretation of your description of the worksheet, I've created a small test worksheet that looks like

  Name Value
    a   abc
    b  12.3
    c   1.2
    d   0.1

where the numbers in the Value column have more significant figures than shown. You seem to have two problems, first the one you ask about as to how to read this, second, how to store the results in R. The columns in R data frames must be all of the same data type and a list would seem a bit messy. A way to do this is to read the worksheet twice, first time to getting all data in Value col as character, the second using the forceConversion option in the readWorksheet function to force the column to be read as numeric and storing the results in a new col. The code looks like

 library(XLConnect)
wb <- loadWorkbook("test.xlsx")
df <- readWorksheet(wb, "test" )
df <- cbind(df, Value_Num=readWorksheet(wb, "test", drop="Name", colTypes="numeric", forceConversion=TRUE)$Value)

This should give df as

 Name Value Value_Num
1    a   abc        NA
2    b  12.3    12.300
3    c   1.2     1.230
4    d   0.1     0.123

where the data in the Value column are character and the data in the Value_Num column are numeric. From there you'll have to sort out what to do with the two Value columns.