CODEWITHSUNDEEP
rexcel
Alexandros
Alexandros

Reputation: 345

read.xlsx file with one column consisting "numbers as text"

I have excel file that contains numeric variables, but the first column (index column) uses custom formatting: those are numbers that should be presented as text (or similar to text) and having always fixed number of digits where some are zeroes. Here is my example table from excel:

enter image description here

And here is formatting for bad_col1 (rest are numbers or general):

enter image description here

When I try to import my data by using read.xlsx function from either openxlsx or xlsx package it produces something like this:

read.xlsx(file_dir,sheet=1)#for openxlsx

  bad_col1 col2 col3
1        5   11  974
2      230   15  719
3    10250    6  944
4     2340    7  401

So as you can see, zeroes are gone. Is there any way to read 1st column as "text" and as other numeric? I can not convert it to text after, because "front zeroes" are gone arleady. I can think of workaround, but it would be more feasible for my project to have them converted while importing.

Thank you in Advance

Upvotes: 2

Views: 2243

Answers (2)

Alexis
Alexis

Reputation: 2254

You can use a vector to filter your desired format, with library readxl:

library(readxl)
filter <- c('text','numeric','numeric')
the_file <- read_xlsx("sample.xlsx", col_types = filter)

Even more, you can skip columns if you use in your filter 'skip' in the desired position, considering that you might have many columns.

Regards

Upvotes: 3

Wolfgang Arnold
Wolfgang Arnold

Reputation: 1252

With this https://readxl.tidyverse.org/reference/read_excel.html you can use paramater col_types so that first column is read as character.

Upvotes: 2

Related Questions