0

I am importing data from Excel. I am using the package readxl to read the data. I have a column of data with both numbers as well as percentage values. Therefore, I want to import this column of data as text.

I used the instructions on the package documentation page how to define column types when importing data. However, even when the data is imported as text, it still converts the percentage to a number.

This is an example of what the data look like the field I am having trouble with:

data <- c(3200, 100%, 100, 100%)

The data frame after the import is showing that it is a character and not numeric. The results I expect would be the same as the vector above. However, what I am seeing is as follows:

output_after_import <- c(3200, 1, 100, 1)

When looking at the data in Excel, when I change the data type from "General" to "Text," the same problem occurs as shown in "output_after_import."

Improve this question
4
  • I think you can specify the colClass Commented Aug 1, 2019 at 15:50
  • can you open the excel file and save it as a text column Commented Aug 1, 2019 at 15:51
  • 2
    The problem is not in readxl as much as it is in Excel. In Excel (as much as in R), what you see (100 and 100%) is just a representation of the data. Internally, it is being stored numerically (100 and 1.0). So your issue isn't with R, it's with Excel. As Mike suggested, I believe your best step forward at this point is to tell Excel what format you want it stored as. (Using openxlsx produces the same results. Perhaps you can find a way to read what Excel thinks the formatting is for each cell, and adjust accordingly. Idk how yet.) Commented Aug 1, 2019 at 16:06
  • Probably easy way out is to use the Import Dataset option in RStudio., in the data preview window, choose character for the column containing the "100%" and import data. This will generate code in the preview window as appropriate that you can use for fetching data. Commented Aug 1, 2019 at 17:34

1 Answer 1

Reset to default
0

Try this: 

library(readxl) ExcelFile <- read_excel("ExcelFile.xlsx", col_types = c("numeric", "text"))
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.