library(tidyverse)
library(readxl)15 Advanced data import I: spreadsheets
Objectives
- Recognize why spreadsheets are common and problematic. Data often arrives as Excel or Google Sheets files. Compared to CSVs, spreadsheets can contain multiple worksheets, extraneous formatting, and mixed data types. Understand why a clean import requires carefully choosing the right sheet, range, and column names.
- Load Excel spreadsheets into R. Use the
readxlpackage to read.xlsand.xlsxfiles withread_xls(),read_xlsx(), and the convenience wrapperread_excel(). Specify column names, skip rows, and define custom missing-value strings when importing. - Work with multiple worksheets. List the sheet names in a workbook with
excel_sheets(), read individual sheets withread_excel(), and combine them withbind_rows(). Recognize that a sheet’s position in the workbook is not a reliable substitute for its name. - Read part of a sheet. Use the
rangeargument to select a specific rectangle of cells when a worksheet contains extraneous text above or below the data. - Understand type guessing and specify column types. Excel cells can contain booleans, numbers, datetimes, or strings; readxl guesses column types but you can override them. Recognize how a stored Excel date can surface as a meaningless raw number instead of an actual date.
- Write Excel files. Create spreadsheets with
writexl::write_xlsx()and know its limitations; know when to reach foropenxlsxinstead for more advanced formatting. - Load data from Google Sheets. Use the
googlesheets4package to read public or shared spreadsheets;read_sheet()mirrorsread_excel()’s interface closely enough that most of what you learn here transfers directly.
Notes
Why spreadsheets?
Plain text formats like CSV are easy to version control and read into R, but many collaborators work in spreadsheets instead. A spreadsheet file can hold multiple worksheets, merge cells for visual layout, and mix formatting in with data. That flexibility is convenient for a person skimming the file, and considerably less convenient for code, which needs to know exactly which worksheet holds the data, whether there are header or footer rows to skip, and whether each column actually represents one single variable.
Reading Excel spreadsheets
readxl provides three functions: read_xls() for the older .xls format, read_xlsx() for the newer .xlsx format, and read_excel(), which detects the file type automatically. All three return a tibble with a consistent interface: the first argument is a path, and the optional sheet argument selects a worksheet by name or position.
path <- readxl_example("datasets.xlsx")
excel_sheets(path) # a workbook can hold several unrelated tables[1] "mtcars" "chickwts" "quakes" read_excel(path, sheet = "chickwts") |> head()# A tibble: 6 × 2
weight feed
<dbl> <chr>
1 179 horsebean
2 160 horsebean
3 136 horsebean
4 227 horsebean
5 217 horsebean
6 168 horsebeanReal spreadsheets are rarely this tidy. readxl’s own deaths.xlsx example file has explanatory text scattered above and below the actual data table:
deaths_path <- readxl_example("deaths.xlsx")
read_excel(deaths_path)New names:
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`# A tibble: 18 × 6
`Lots of people` ...2 ...3 ...4 ...5 ...6
<chr> <chr> <chr> <chr> <chr> <chr>
1 simply cannot resist writing <NA> <NA> <NA> <NA> some…
2 at the top <NA> of thei…
3 or merging <NA> <NA> <NA> cells
4 Name Profession Age Has … Date… Date…
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 <NA> <NA> <NA> <NA> <NA>
16 <NA> also like to write stuff <NA> <NA> <NA> <NA>
17 <NA> <NA> at t… bott… <NA> <NA>
18 <NA> <NA> <NA> <NA> <NA> too! Reading the sheet with no further guidance pulls in the stray text rows as if they were data, and produces a column-name repair warning worth reading rather than dismissing (see Example 14.2). skip (to drop leading rows), col_names (to supply your own names outright), and na (to mark additional strings as missing) clean this kind of import up, the same way they do for read_csv().
Reading part of a sheet with range
When the real data occupies only a rectangle of the sheet, range reads exactly that rectangle and nothing else.
read_excel(deaths_path, range = "A5:F15")# A tibble: 10 × 6
Name Profession Age `Has kids` `Date of birth` `Date of death`
<chr> <chr> <dbl> <lgl> <dttm> <dttm>
1 David Bo… musician 69 TRUE 1947-01-08 00:00:00 2016-01-10 00:00:00
2 Carrie F… actor 60 TRUE 1956-10-21 00:00:00 2016-12-27 00:00:00
3 Chuck Be… musician 90 TRUE 1926-10-18 00:00:00 2017-03-18 00:00:00
4 Bill Pax… actor 61 TRUE 1955-05-17 00:00:00 2017-02-25 00:00:00
5 Prince musician 57 TRUE 1958-06-07 00:00:00 2016-04-21 00:00:00
6 Alan Ric… actor 69 FALSE 1946-02-21 00:00:00 2016-01-14 00:00:00
7 Florence… actor 82 TRUE 1934-02-14 00:00:00 2016-11-24 00:00:00
8 Harper L… author 89 FALSE 1926-04-28 00:00:00 2016-02-19 00:00:00
9 Zsa Zsa … actor 99 TRUE 1917-02-06 00:00:00 2016-12-18 00:00:00
10 George M… musician 53 FALSE 1963-06-25 00:00:00 2016-12-25 00:00:00This is the more direct fix for the deaths.xlsx example above: rather than guessing at skip and hoping nothing extraneous remains below the data, naming the exact cell range sidesteps the stray text entirely, on both ends.
Working with multiple worksheets
excel_sheets() lists every sheet in a workbook. When the same kind of data is split across several sheets, such as one sheet per site or per month, reading each sheet into its own tibble and combining them with bind_rows() is the standard pattern:
path <- "data/penguins.xlsx"
sheet_names <- excel_sheets(path)
penguins <- sheet_names |>
set_names() |>
map(~ read_excel(path, sheet = .x, na = "NA")) |>
bind_rows(.id = "island")Passing .id = "island" to bind_rows() records which sheet each row came from, which matters here since the sheet name (the island) isn’t otherwise a column in any individual sheet.
Data types and type guessing
Unlike a CSV, an Excel cell natively holds a boolean, a number, a datetime, or text, and readxl guesses each column’s type from a sample of its cells, the same way read_csv() does. Mixed or unusual values can confuse that guess (see Example 14.3), and col_types overrides it explicitly when needed.
Internally, Excel stores every date as a plain number, a count of days since a fixed reference date, and only displays it as a date because of cell formatting that a plain data import can’t see. When readxl successfully recognizes a column as a date, it converts that number back for you automatically; when it doesn’t, you’re left holding a meaningless-looking integer instead of a date, with no error to flag it (see Example 14.1).
Writing Excel files
writexl::write_xlsx() writes a data frame to a genuine .xlsx file with simple formatting, and reading it back confirms the round trip.
library(writexl)
bake_sale <- tibble(
item = factor(c("brownie", "cupcake", "cookie")),
quantity = c(10, 5, 8)
)
xlsx_path <- tempfile(fileext = ".xlsx")
write_xlsx(bake_sale, path = xlsx_path)
read_excel(xlsx_path)# A tibble: 3 × 2
item quantity
<chr> <dbl>
1 brownie 10
2 cupcake 5
3 cookie 8If you need finer control, multiple worksheets in one file, or cell styling, openxlsx provides functions for column widths, fonts, and colors that write_xlsx() intentionally leaves out in favor of simplicity.
Google Sheets
Many teams collaborate in Google Sheets rather than local Excel files. The googlesheets4 package’s read_sheet() mirrors read_excel()’s interface closely: it accepts col_names, skip, na, and col_types, and takes a Google Sheets URL or file ID in place of a local path.
library(googlesheets4)
gs4_deauth() # skip authentication for a publicly shared sheet
sheet_id <- "1V1nPp1tzOuutXFLb3G9Eyxi3qxeEhnOXUzL5_BcCQ0w"
students <- read_sheet(
sheet_id,
col_names = c("student_id", "full_name", "favourite_food", "meal_plan", "age"),
skip = 1,
na = c("", "N/A"),
col_types = "dcccc"
)read_sheet() needs an active internet connection, and a handful of functions (sheet_names(), sheet_write()) exist only on the Google Sheets side with no readxl equivalent, but most of what you already know about read_excel() transfers directly.
Fringe cases and common pitfalls
ExampleExample 14.1
A date stored in Excel can surface as a meaningless raw number.
The clippy.xlsx example file lays out its data as label-value pairs rather than one column per variable, which defeats readxl’s date-guessing entirely, since it never sees a whole column of consistent dates to recognize:
clippy_path <- readxl_example("clippy.xlsx")
read_excel(clippy_path)# A tibble: 4 × 2
name value
<chr> <chr>
1 Name Clippy
2 Species paperclip
3 Approx date of death 39083
4 Weight in grams 0.9 "39083" is not a typo or a strange unit; it’s the literal number of days Excel has counted since its internal reference date, with the date formatting that would normally display it as a real date stripped away by this layout. Converting it back is a one-line fix once you know to look for it:
as.Date(39083, origin = "1899-12-30")[1] "2007-01-01"1899-12-30 (not 1900-01-01) is Excel’s actual epoch, offset by a long-standing historical bug in Excel’s leap-year handling that Microsoft never fixed, for backward compatibility. Whenever a numeric column is suspiciously close to five digits and the surrounding columns are clearly about dates, suspect this before assuming the number means something else entirely.
ExampleExample 14.2
Blank or duplicate header cells get silently renamed, and the warning is easy to miss.
Reading deaths.xlsx without a range pulls in a row of genuinely blank header cells above the real data:
read_excel(deaths_path) |> names()New names:
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`[1] "Lots of people" "...2" "...3" "...4"
[5] "...5" "...6" readxl can’t leave a column with no name at all, so it invents ...2, ...3, and so on, and reports having done so in a “New names” message that looks more like routine console output than an actual warning. If you don’t look closely, it is easy to proceed with a data frame whose columns are named ...2 through ...6 without ever noticing that the real headers were sitting a few rows further down, exactly where range = "A5:F15" (used earlier in this session) finds them correctly.
ExampleExample 14.3
A handful of inconsistent values can collapse an entire column’s guessed type to character.
readxl’s type-me.xlsx example is built specifically to demonstrate this: a column that’s mostly boolean-looking values, with a few outliers mixed in.
type_path <- readxl_example("type-me.xlsx")
read_excel(type_path, sheet = "logical_coercion")# A tibble: 10 × 2
`maybe boolean?` description
<chr> <chr>
1 <NA> "empty"
2 0 "0 (numeric)"
3 1 "1 (numeric)"
4 40908 "datetime"
5 TRUE "boolean true"
6 FALSE "boolean false"
7 cabbage "\"cabbage\""
8 true "the string \"true\""
9 F "the letter \"F\""
10 False "\"False\" preceded by single quote"Values like TRUE, FALSE, 0, and 1 all look like they belong in a logical column, but "cabbage" and "F" (a bare letter, not FALSE) do not, so readxl falls back to reading the entire column as character rather than guessing logical and silently mangling the outliers. This is the same all-or-nothing behavior you saw with read_csv()’s numeric guessing back in Session 7: one inconsistent value is enough to change how every other value in the column gets interpreted, not just its own cell.
ExampleExample 14.4
Reading a sheet by position instead of by name can silently give you the wrong table.
excel_sheets(path) # recall: "mtcars", "chickwts", "quakes", in that order[1] "mtcars" "chickwts" "quakes" read_excel(path, sheet = 1) |> names() # not chickwts! [1] "mpg" "cyl" "disp" "hp" "drat" "wt" "qsec" "vs" "am" "gear"
[11] "carb"read_excel(path, sheet = "chickwts") |> names()[1] "weight" "feed" sheet = 1 reads whichever sheet happens to be first in the workbook, which is mtcars here, not chickwts. Nothing about this errors or warns, because sheet = 1 is a perfectly valid request; it just isn’t the request you meant if you assumed a specific dataset would always be first. Referring to a sheet by its name rather than its position is worth the extra few characters of typing, especially for a workbook someone else maintains and might reorder later without telling you.
Recap
| Term | Definition |
|---|---|
read_excel() |
Reads an Excel file (.xls or .xlsx), auto-detecting the format; sheet selects a worksheet by name or position. |
excel_sheets() |
Lists every worksheet name in a workbook, without reading any of the data. |
range |
Reads only a specific rectangle of cells (for example, "A5:F15"), skipping extraneous rows or columns entirely. |
| Column name repair | readxl automatically renames blank or duplicate header cells to ...2, ...3, and so on, reporting the change in a “New names” message. |
| Excel date storage | Internally just a count of days since 1899-12-30 (Excel’s epoch); displayed as a date only through cell formatting that a plain import can’t see. |
col_types |
Overrides readxl’s guessed type for one or more columns, the same role col_types plays in read_csv(). |
write_xlsx() |
Writes a data frame to a simple .xlsx file; use openxlsx for advanced formatting writexl doesn’t support. |
read_sheet() |
googlesheets4’s equivalent of read_excel(), reading a Google Sheet by URL or ID over the internet. |
Check your understanding
NoteProblems
- A workbook has explanatory text above and below the actual data table on its only worksheet. Name two different
read_excel()arguments that could clean this up, and explain how they differ. - A colleague imports a spreadsheet and finds a column full of numbers like
44561where they expected dates. What almost certainly happened, and how would they fix it? read_excel()prints a “New names” message listing...2and...3after you import a sheet. What triggered this, and what should you do about it?- A column that should be logical (
TRUE/FALSE) comes back fromread_excel()as character instead. What is the most likely explanation, and how would you find the specific values responsible? - Why is
read_excel(path, sheet = "sales")generally safer thanread_excel(path, sheet = 2), even if sheet 2 happens to be named “sales” today?
TipSolutions
skipdrops a fixed number of rows from the top of the sheet before reading, which works well when the extraneous content is only above the data.rangeinstead reads one specific rectangle of cells, handling extraneous content both above and below (or on either side of) the data in a single argument, without needing to know exactly how many rows to skip.The column almost certainly holds genuine Excel dates that readxl did not recognize as dates, so what’s showing up is the raw internal representation, a count of days since Excel’s epoch. Converting it with
as.Date(x, origin = "1899-12-30")recovers the actual date.The sheet had one or more blank (or duplicate) header cells in its first row, and readxl cannot leave a column with no name at all, so it invented placeholder names. This is a signal that you probably haven’t found the real header row yet; check whether a
rangeorskipargument would land you on the actual header instead.Some value in that column doesn’t look like a boolean at all (a stray word, a letter that isn’t exactly
TRUE/FALSE, or similar), so readxl fell back to reading the whole column as character rather than guessing logical and mangling just the one inconsistent cell. Reading the column in and then filtering for values that aren’t"TRUE","FALSE","0", or"1"(as text) would surface the specific offending values.A sheet’s position in a workbook can change if someone inserts, deletes, or reorders sheets later, silently pointing
sheet = 2at a completely different table with no error or warning. A sheet’s name is far less likely to change casually, and if it does change, code that refers to it by name fails loudly (with a clear “sheet not found” error) instead of silently reading the wrong data.