readxl::read_excel() will guess column types, by default, or you can provide them explicitly via the col_types argument. While this is easy enough for tall datasets, with wider dataframes you want to transform only a few column types after the import using as.character or as.numeric type mutations. When guessing, Hereâs how the Excel cell/column types are translated into R types and how to force the type explicitly in Some explanation about the weird cases in the first two rows:Final note: all datetimes are imported as having the UTC timezone, because, mercifully, Excel has no notion of timezones.Itâs pretty common to expect a column to import as, say, numeric or datetime.
It picked the first row as Header i.e., column names for the columns. If you really desire data loss (e.g. Two main causes:Non-data rows above and below the main data rectangle are causing all the columns to import as character.If your column typing problem canât be solved by specifying Sometimes you arenât completely sure of column count or order, and yet you need to provide Hereâs an efficient trick to get the column names, so you can programmatically build the You can force a column to have a specific type via For each column type, below we present a screen shot of a sheet from the built-in example #> Name Profession Age `Has kids` `Date of birth` `Date of death` #> #> 1 Davi⦠musician 69 TRUE 1947-01-08 00:00:00 2016-01-10 00:00:00#> 2 Carr⦠actor 60 TRUE 1956-10-21 00:00:00 2016-12-27 00:00:00#> 3 Chuc⦠musician 90 TRUE 1926-10-18 00:00:00 2017-03-18 00:00:00#> 4 Bill⦠actor 61 TRUE 1955-05-17 00:00:00 2017-02-25 00:00:00#> `Lots of people` ...2 ...3 ...4 ...5 ...6 #> #> 1 simply cannot resist ⦠some notes #> 2 at the top of their spreâ¦#> 3 or merging cells #> 4 Name Profession Age Has k⦠Date of⦠Date of deâ¦#> 5 David Bowie musician 69 TRUE 17175 42379 #> 6 Carrie Fisher actor 60 TRUE 20749 42731 #> 7 Chuck Berry musician 90 TRUE 9788 42812 #> 8 Bill Paxton actor 61 TRUE 20226 42791 #> 9 Prince musician 57 TRUE 21343 42481 #> 10 Alan Rickman actor 69 FALSE 16854 42383 #> 11 Florence Henderson actor 82 TRUE 12464 42698 #> 12 Harper Lee author 89 FALSE 9615 42419 #> 13 Zsa Zsa Gábor actor 99 TRUE 6247 42722 #> 14 George Michael musician 53 FALSE 23187 42729 #> 15 Some #> 16 also like to ⦠#> 17 at the botto⦠#> 18 too!#> [1] "Sepal.Length" "Sepal.Width" "Petal.Length" "Petal.Width" #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species#> #> 1 5.1 3.5 1.4 0.2 setosa #> 2 4.9 3 1.4 0.2 setosa #> 3 4.7 3.2 1.3 0.2 setosa #> 4 4.6 3.1 1.5 0.2 setosa #> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =#> sheet, : Expecting logical in A5 / R5C1: got a date#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =#> sheet, : Expecting logical in A8 / R8C1: got 'cabbage'#> `maybe boolean?` description #> #> 1 NA empty #> 2 FALSE 0 (numeric) #> 3 TRUE 1 (numeric) #> 4 NA datetime #> 5 TRUE boolean true #> 6 FALSE boolean false #> 7 NA "\"cabbage\"" #> 8 TRUE "the string \"true\"" #> 9 FALSE "the letter \"F\"" #> 10 FALSE "\"False\" preceded by single quote"#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =#> sheet, : Coercing boolean to numeric in A3 / R3C1#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =#> sheet, : Coercing boolean to numeric in A4 / R4C1#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =#> sheet, : Expecting numeric in A5 / R5C1: got a date#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =#> sheet, : Coercing text to numeric in A6 / R6C1: '123456'#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =#> sheet, : Expecting numeric in A8 / R8C1: got 'cabbage'#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =#> sheet, : Expecting date in A5 / R5C1: got boolean#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =#> sheet, : Expecting date in A6 / R6C1: got 'cabbage'#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =#> Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i = First column for Double. reading 3.14 as integer ) you have to truncate such columns afterwards yourself explicitly so that this is clear to future readers of your code. You can do this with col_types.If set to NULL, the default, functions from the readr package will try to find the correct types themselves.