Reading CSV and Delimited Files

Maria runs a small corner bakery. Every Sunday her till exports the week's sales as a file called sales.csv, and she emails it to herself to add up the totals. Before R can total anything, it has to read that file, and reading it well is the whole skill.

Here is the catch that makes it a skill: a CSV stores everything as plain text, even the numbers and the dates. Maria's revenue column arrives as text like $1,250. Reading the file well means turning that text into the real numbers and dates you can actually compute with. The panel below shows one piece of that. Press the buttons.

By the end of this lesson you will be able to:

• Say what a CSV / delimited file is, and read one into R in a single line
• Read and control the column types R assigns, and override a wrong guess
• Diagnose and fix parsing problems and missing-value codes
• Read files that use other delimiters: semicolons, tabs, or any character at all

Prerequisites: you can run R and load a package with library(), and you have seen a table of rows and columns before. Every new term is defined as it appears.

A CSV is just text

Open sales.csv in a plain text editor and there is no grid, no spreadsheet, just lines of text. CSV stands for comma-separated values, and the format is exactly that literal:

• Each line is one row of data.
• Within a line, the values are split by a delimiter, a single character that marks where one field ends and the next begins. For a CSV that character is the comma.
• The very first line is usually a header: it names the columns rather than holding data.

Let us create Maria's file right here so we have something real to read, then print it back exactly as it sits on disk. Run this once.

That header line, date,item,qty,price, is the promise the file makes: four columns, in that order. Your job is to turn those seven lines of text into a proper table.

Read it in one line

The readr package (part of the tidyverse) reads a CSV with a single function, read_csv(). Give it the file name and it hands back a tibble, which is just a tidy data frame: a table of equal-length columns.

Look at the small grey line under the column names: <date>, <chr>, <dbl>, <dbl>. That is the type readr assigned to each column, and it is the single most important thing on the screen.

• <chr> is text (a character column), here the item names.
• <dbl> is a number that can carry decimals (a "double"), here both qty and price. readr reads every plain number as a double, even a whole count like 14.
• <date> is a real calendar date, not text, so R can sort it and do date arithmetic.

readr never guesses the whole-number type (<int>) on its own; it always reads a bare number as a double. If you want a column kept as true integers you ask for it by hand, which is the next step.

Where did the types come from?

You ran sales <- read_csv("sales.csv") and the printout shows qty <dbl> and date <date> under the column names. Where did those types come from?

Take control with col_types

readr's guess is right most of the time, but not always, and a wrong type can quietly corrupt your data. Two classics:

1. A date in a non-standard format, like 03/09/2026, is not the year-month-day shape readr expects, so it gives up and leaves the column as plain text. The widget below shows that exact column rescued.
2. A code with leading zeros, like the product code 00123, looks numeric, so readr reads it as the number 123 and silently throws the zeros away.

The fix for both is the col_types argument: you name the type you want and readr obeys instead of guessing. There are two ways to write it. The long, readable form uses cols() with one col_*() per column; the short form is a string with a single letter per column (D date, c character, i integer, d double).

For the non-standard date, you tell readr the layout with col_date(format = ...), using %m for month, %d for day and %Y for a four-digit year:

Lock in the column types

Maria's sales.csv is already on disk from earlier. Pin down all four column types by hand so nothing is left to a guess. The date, item and quantity are filled in. Complete the spec so price is read as a decimal number, then check it.

sales <- read_csv("sales.csv", col_types = cols(
  date  = col_date(),
  item  = col_character(),
  qty   = col_integer(),
  price = col_double()
))

Parsing problems and missing values

Real exports are messy. Suppose one quantity was never recorded and the till wrote n/a instead. Watch what readr does with the qty column.

qty came back as <chr>, text, not a number. readr only treats a few strings as missing by default (an empty cell and NA). It has never heard of n/a, so to readr the column contains the words 14, n/a and 6, and a column with a word in it cannot be numeric. The cure is to tell readr which strings mean "missing" with the na argument:

Now n/a becomes a true NA (R's marker for a missing value), the rest of the column is all numbers, and qty reads as <dbl>. When you instead force a type that a value cannot fit, readr does not crash: it inserts NA and records the trouble in a report you can read with problems().

The n/a column

A file has n/a in its qty column. You run read_csv("file.csv") and qty comes back as text (<chr>) instead of a number. Why, and what is the cleanest fix?

Other delimiters

The "C" in CSV is only a convention. The delimiter can be any character, and two come up constantly. In much of Europe the comma is the decimal point (one euro twenty is written 1,20), so spreadsheets there separate fields with a semicolon instead. read_csv2() is the same reader tuned for that: semicolons split the fields and a comma is read as a decimal point.

For any other delimiter, name it yourself with read_delim() and its delim argument. A tab-separated file is so common it has its own shortcut, read_tsv(), which is just read_delim(delim = "\t").

Everything you learned about types, col_types and missing values works identically with all of these readers, only the delimiter changes.

Pick the right reader

Maria's flour supplier in Munich sends supplier.csv with semicolons between the fields and a comma for the decimal point (1,20 means one euro twenty). Fill in the one reader that handles both at once, then check it.

supplier <- c("item;price", "flour;1,20", "butter;3,50")
writeLines(supplier, "supplier.csv")
read_csv2("supplier.csv")

References

A few authoritative places to take this further:

• R for Data Science (2e), Data import - the canonical, free walkthrough of reading data with readr, by the people who wrote it.
• readr package home (tidyverse) - the function reference and the one-page data-import cheatsheet.
• readr column-type reference: cols() and col_*() - every type specifier, including dates, factors and skipping columns.
• RFC 4180: the CSV format - the short formal definition of what a comma-separated file actually is, quoting and edge cases included.

Lesson 1 complete

You can now turn a file of plain text into a proper R table: read it with read_csv(), read and control the column types with col_types, rescue messy values with na and problems(), and switch readers when the delimiter changes.

That covers the format most data arrives in. But plenty of it does not: spreadsheets, statistical software files, and more.

Next, Lesson 2: Reading Excel and other formats. You will open real .xlsx workbooks (picking the sheet and range you want) and read SPSS, Stata and SAS files, reusing every idea about column types you just learned.