# Export to CSV in Rails (the right way, at any scale)

Exporting data to CSV is one of those features every Rails app grows into. Someone on the team wants the users list in a spreadsheet, finance wants last month's orders, support wants to hand a customer their own records. The good news is that Ruby ships almost everything we need. The catch is that the version of "export to CSV" you find in most tutorials works beautifully on your laptop with 200 rows and then quietly falls over when a real table has half a million.

This guide starts with that quick version, because for small exports it is the right amount of code. Then we take it to the scale where it breaks, show exactly where the memory goes (with numbers we measured), and rebuild it so a million rows costs the same memory as a hundred. We finish with the two details every tutorial skips and every user notices: broken characters in Excel, and CSV files that can run formulas on the person who opens them.

{{takeaways}}
<ul>
<li><span><strong>Small export?</strong> A model <code>to_csv</code> method with <code>CSV.generate</code> plus <code>send_data</code> in the controller is all you need.</span></li>
<li><span><strong>Large export?</strong> <code>send_data</code> holds the entire file in memory. We measured a 1M-row export retaining ~76 MB per request. Stream the response with <code>send_stream</code> and read rows with <code>find_each</code> to keep it flat at ~1 MB.</span></li>
<li><span><strong>Very large or slow export?</strong> Move it to a background job, write the file to storage, and hand the user a download link.</span></li>
<li><span><strong>Don't forget:</strong> add a UTF-8 BOM so Excel renders accents correctly, and sanitize cells so a value like <code>=cmd()</code> can't run as a formula.</span></li>
</ul>
{{/takeaways}}

## A note before we start: the csv gem

Ruby's `CSV` library used to be always available. As of Ruby 3.4.0 it became a bundled gem, which means you now have to declare it. If you are on Ruby 3.4 or newer, add it to your Gemfile:

```ruby
# Gemfile
gem "csv"
```

Then `bundle install`. On older Rubies a bare `require "csv"` still works, but adding the line is harmless and future-proofs the app.

## The quick version (and when it is enough)

Let's export a `users` table. First we register the CSV format so Rails knows what a `.csv` request means. Most recent Rails versions already register it, but being explicit never hurts:

```ruby
# config/initializers/mime_types.rb
Mime::Type.register "text/csv", :csv
```

Next, a class method on the model that turns a collection into a CSV string. `CSV.generate` builds the whole document and returns it as a `String`:

```ruby
# app/models/user.rb
require "csv"

class User < ApplicationRecord
  def self.to_csv
    CSV.generate do |csv|
      csv << column_names
      all.each { |user| csv << user.attributes.values }
    end
  end
end
```

Finally, the controller responds to a `.csv` request by sending that string as a download. `send_data` (from `ActionController::DataStreaming`) sets the download headers for us, and the `filename` option names the file the browser saves:

```ruby
# app/controllers/users_controller.rb
def index
  @users = User.all

  respond_to do |format|
    format.html
    format.csv do
      send_data @users.to_csv, filename: "users-#{Date.current}.csv"
    end
  end
end
```

Add a link to `users_path(format: :csv)` and you have a working export. For an admin screen listing a few hundred or a few thousand rows, stop here. This is clear, conventional Rails, and rewriting it to be fancier would be effort you do not need to spend. Reach for the rest of this guide when the table gets big or the export starts timing out.

## Where the quick version breaks

The problem is memory, and it hides in two places at once.

The first sink is `User.all`. Calling `.all.each` loads every matching record into a Ruby array before you touch a single one. The second sink is `CSV.generate`: it concatenates the entire file into one `String` and keeps it in memory until `send_data` finishes writing it to the socket. So at the peak you are holding all the records *and* the whole rendered file at the same time.

To put real numbers on it, we ran a benchmark on Ruby 3.4.7 comparing two ways of producing the exact same CSV: building the whole string in memory (what `send_data` does) versus writing it out one row at a time. We measured the resident memory retained at the point each approach holds the most, plus wall time.

<div class="overflow-x-auto my-6">
<table class="min-w-full divide-y divide-gray-200">
  <thead>
    <tr class="bg-white">
      <th scope="col" class="px-4 py-3 text-left text-xs font-medium text-slate-600 uppercase tracking-wider">Rows</th>
      <th scope="col" class="px-4 py-3 text-left text-xs font-medium text-slate-600 uppercase tracking-wider">CSV file size</th>
      <th scope="col" class="px-4 py-3 text-left text-xs font-medium text-slate-600 uppercase tracking-wider">In-memory (retained RSS)</th>
      <th scope="col" class="px-4 py-3 text-left text-xs font-medium text-slate-600 uppercase tracking-wider">Streaming (retained RSS)</th>
      <th scope="col" class="px-4 py-3 text-left text-xs font-medium text-slate-600 uppercase tracking-wider">In-memory time</th>
      <th scope="col" class="px-4 py-3 text-left text-xs font-medium text-slate-600 uppercase tracking-wider">Streaming time</th>
    </tr>
  </thead>
  <tbody class="bg-white divide-y divide-gray-200 text-sm text-slate-700">
    <tr class="hover:bg-gray-50 transition-colors">
      <td class="px-4 py-3 whitespace-nowrap">100,000</td>
      <td class="px-4 py-3 whitespace-nowrap">7 MB</td>
      <td class="px-4 py-3 whitespace-nowrap">10 MB</td>
      <td class="px-4 py-3 whitespace-nowrap">1 MB</td>
      <td class="px-4 py-3 whitespace-nowrap">0.33s</td>
      <td class="px-4 py-3 whitespace-nowrap">0.36s</td>
    </tr>
    <tr class="hover:bg-gray-50 transition-colors">
      <td class="px-4 py-3 whitespace-nowrap">500,000</td>
      <td class="px-4 py-3 whitespace-nowrap">36 MB</td>
      <td class="px-4 py-3 whitespace-nowrap">39 MB</td>
      <td class="px-4 py-3 whitespace-nowrap">1 MB</td>
      <td class="px-4 py-3 whitespace-nowrap">1.78s</td>
      <td class="px-4 py-3 whitespace-nowrap">1.77s</td>
    </tr>
    <tr class="hover:bg-gray-50 transition-colors">
      <td class="px-4 py-3 whitespace-nowrap">1,000,000</td>
      <td class="px-4 py-3 whitespace-nowrap">73 MB</td>
      <td class="px-4 py-3 whitespace-nowrap">76 MB</td>
      <td class="px-4 py-3 whitespace-nowrap">1 MB</td>
      <td class="px-4 py-3 whitespace-nowrap">3.25s</td>
      <td class="px-4 py-3 whitespace-nowrap">3.88s</td>
    </tr>
  </tbody>
</table>
</div>

Here is the whole benchmark, so you can run it against your own Ruby and row shape:

```ruby
# csv_bench.rb
# run: ruby csv_bench.rb memory 1000000
#      ruby csv_bench.rb stream 1000000
require "csv"
require "tempfile"

mode = ARGV[0]                        # "memory" (send_data) or "stream"
n    = (ARGV[1] || "1000000").to_i
rss  = -> { `ps -o rss= -p #{Process.pid}`.to_i } # resident memory, KB

row = ->(i) { [i, "User #{i}", "user#{i}@example.com", "active"] }
GC.start
out = Tempfile.new(["export", ".csv"])

if mode == "memory"
  string = CSV.generate { |csv| n.times { |i| csv << row.call(i) } } # whole file in RAM
  peak = rss.call
  out.write(string)
else
  CSV.open(out.path, "w") { |csv| n.times { |i| csv << row.call(i) } } # one row at a time
  peak = rss.call
end

puts "#{mode}: #{peak / 1024} MB retained for #{n} rows"
```

Two things stand out. The in-memory build grows in lockstep with the export: roughly the whole file sits in your process, so a 1M-row export costs about 76 MB. Streaming stays flat at around 1 MB no matter how many rows go through it. Time is basically the same either way, so you are not trading speed for memory, you are just choosing whether to hold the file or let it flow.

{{warning}}
That 76 MB is per request. A web server like Puma runs several threads or workers, so a handful of people clicking "Export" at the same time multiplies the number. This is exactly how an export feature that worked in staging takes down production the first time a big customer uses it.
{{/warning}}

We fix the two sinks one at a time.

## Fix one: stop loading every record

The query side is the easy half. Active Record's `find_each` loads records in batches (1000 at a time by default) and yields them one by one, so you never hold the full table in memory. On [Active Record models](https://avohq.io/glossary/models) it is a drop-in replacement for `all.each`:

```ruby
# app/models/user.rb
def self.to_csv
  CSV.generate do |csv|
    csv << column_names
    find_each { |user| csv << user.attributes.values }
  end
end
```

One caveat before you swap it in everywhere: `find_each` batches by primary key and ignores any `order` you set on the query. If the export has to come out sorted a particular way, sort the finished file, or keep the plain in-memory version for that one case where the row count is small enough to afford it.

This bounds the record side, but `CSV.generate` is still building one big string, so the file itself is still fully in memory. For that we have to stop returning a string at all.

## Fix two: stream the response

Instead of building the whole file and then sending it, we write each row to the response as we produce it. Rails 7.2 added [`send_stream`](https://api.rubyonrails.org/classes/ActionController/Live.html#method-i-send_stream) for exactly this. It comes from `ActionController::Live`, so we include that module in the controller. We use `CSV.generate_line` to turn each row into a single line of CSV text and write it straight to the stream:

```ruby
# app/controllers/exports_controller.rb
class ExportsController < ApplicationController
  include ActionController::Live

  def users
    send_stream(filename: "users-#{Date.current}.csv") do |stream|
      stream.write CSV.generate_line(User.column_names)

      User.find_each do |user|
        stream.write CSV.generate_line(user.attributes.values)
      end
    end
  end
end
```

Now only one row exists in memory at a time, on both the query side and the CSV side. This is the streaming column from the benchmark: flat memory, all the way up.

A few things to know before you ship it:

- **`send_stream` needs `include ActionController::Live`.** That is what defines the method. It is easy to leave out and then get a `NoMethodError`.
- **`Live` changes how the action runs.** It serves the request in its own thread and checks out a separate database connection for the duration, and once the first bytes are sent the response is committed as a `200`, so you cannot switch to an error status if something blows up mid-stream. Keep streaming exports in their own controller (as above) so this behavior does not affect your normal actions.
- **Run behind a non-buffering server.** Puma streams fine. WEBrick buffers the whole response and defeats the point, so do not benchmark this in a bare `rails server` on old setups.
- **Watch your proxy and middleware.** Nginx buffers responses by default; send the `X-Accel-Buffering: no` header to turn it off for the export. `Rack::ETag` also buffers the body to compute a digest, so exclude the export path if you have it enabled.

On Rails older than 7.2 you use the same `ActionController::Live` module, just without the `send_stream` helper: set your headers, write to `response.stream` directly, and close it in an `ensure`.

## For the really big exports: a background job

Streaming keeps memory flat, but the export still runs inside a single web request. If generating the file takes 30 seconds because it joins three tables and formats every row, you are holding a request open (and risking a proxy timeout) the whole time. Past a certain size the right move is to stop making the user wait at all.

The pattern: generate the file in a background job, [store it on S3](https://avohq.io/blog/rails-s3-direct-uploads) with Active Storage, and notify the user with a download link when it is ready.

```ruby
# app/jobs/user_export_job.rb
class UserExportJob < ApplicationJob
  queue_as :default

  def perform(requester)
    export = requester.exports.create!(status: "processing")

    Tempfile.create(["users", ".csv"]) do |file|
      CSV.open(file.path, "w") do |csv|
        csv << User.column_names
        User.find_each { |user| csv << user.attributes.values }
      end
      file.rewind
      export.file.attach(
        io: file,
        filename: "users-#{Date.current}.csv",
        content_type: "text/csv"
      )
    end

    export.update!(status: "ready")
    ExportMailer.ready(export).deliver_later
  end
end
```

Note the `Tempfile`: writing to disk row by row keeps the job's memory flat too, the same principle as streaming, applied to the worker instead of the request. Active Storage then uploads the finished file to your bucket and gives you a URL to email. The user clicks "Export", gets an "we'll email you when it's ready" message, and the web process is free the entire time.

## The two details every tutorial skips

### Excel and encoding

Open a UTF-8 CSV with accented names in Excel on Windows and you will often see `José` turn into `JosÃ©`. The reason is that Excel does not assume UTF-8 unless the file starts with a byte order mark (BOM), the three-byte UTF-8 signature `EF BB BF`. Prepend it and Excel reads the file correctly:

```ruby
BOM = "\uFEFF"

send_data BOM + User.all.to_csv,
  filename: "users-#{Date.current}.csv",
  type: "text/csv; charset=utf-8"
```

For the streaming version, write the BOM as the very first thing on the stream, before the header row. The BOM is a Unicode signature and other tools ignore it, so adding it is safe. This behavior in Excel is not formally documented by Microsoft, but it is consistent enough that shipping the BOM is standard practice for spreadsheets people open in Excel.

### CSV injection

This one is a genuine security issue and almost no export tutorial mentions it. If a cell value starts with `=`, `+`, `-`, or `@`, spreadsheet software (Excel, Google Sheets, LibreOffice) can treat it as a formula. A user who sets their display name to `=IMPORTXML(...)` or a command string can turn your innocent export into code that runs on whoever opens it. OWASP calls this CSV injection, or formula injection.

The mitigation is to neutralize any cell that begins with one of those characters, for example by prefixing it with a single quote so the spreadsheet treats it as text:

```ruby
def csv_safe(value)
  string = value.to_s
  string.match?(/\A[=+\-@\t\r]/) ? "'#{string}" : string
end
```

Run every user-supplied cell through it while building the row:

```ruby
csv << user.attributes.values.map { |value| csv_safe(value) }
```

{{info}}
OWASP is honest that no single escaping trick is perfect across every spreadsheet and version, especially after a file is saved and reopened in Excel. The quote-prefix above stops the formula from executing, which is the part that matters. One tradeoff to know: because `-` is on the list, a legitimate negative number like `-5` becomes the text `'-5`, so skip the sanitizer on columns you know are numeric. If your export is for a known internal audience the risk is lower, but if anyone outside your team can influence a value and then open the file, sanitize it.
{{/info}}

### Putting it together

The snippets above each showed one idea in isolation. Here is the streaming export with all of them wired in: `find_each` batching, the BOM for Excel, and `csv_safe` on every value.

```ruby
# app/controllers/exports_controller.rb
class ExportsController < ApplicationController
  include ActionController::Live

  def users
    send_stream(filename: "users-#{Date.current}.csv", type: "text/csv; charset=utf-8") do |stream|
      stream.write "\uFEFF" # UTF-8 BOM so Excel renders accents
      stream.write CSV.generate_line(User.column_names)

      User.find_each do |user|
        safe_row = user.attributes.values.map { |value| csv_safe(value) }
        stream.write CSV.generate_line(safe_row)
      end
    end
  end

  private

  def csv_safe(value)
    string = value.to_s
    string.match?(/\A[=+\-@\t\r]/) ? "'#{string}" : string
  end
end
```

This is the version to reach for once real users, and the data they control, are going through the export.

## Summary

- For a small export, a model `to_csv` built on `CSV.generate` plus `send_data` in the controller is the whole job. Do not over-build it.
- `send_data` holds the entire file in memory, and `all.each` holds every record. We measured a 1M-row export retaining about 76 MB per request as a result.
- Read records with `find_each` and stream the response with `send_stream` to keep memory flat at around 1 MB regardless of size, at basically the same speed.
- For exports big or slow enough to risk a timeout, move generation to a background job, write to a `Tempfile`, store the file with Active Storage, and email a download link.
- Add a UTF-8 BOM for Excel, and sanitize cells starting with `= + - @` to prevent CSV injection.

## Best practices

- Let users export what they are looking at. If a screen has filters, apply the same scope to the export so the CSV matches the view.
- Select only the columns you need instead of dumping `attributes.values`. It is smaller, faster, and avoids leaking internal fields.
- Put a sensible ceiling on synchronous exports and send anything above it through the background job path.
- Name the file with a date so downloads do not collide in the user's Downloads folder.
- Write a request spec that asserts the header row and one data row. CSV output is easy to break silently when the model changes.

## You might not need to build this at all

Everything above is a few dozen lines, but it is a few dozen lines you now own: the streaming, the batching, the BOM, the injection escaping, the background job, and every edge case that shows up after launch. On a single export that is fine. Across a whole admin or back-office screen, it adds up to a second app you are building and maintaining next to the one your customers use.

That second app is exactly what [Avo](https://avohq.io) is for. Avo is an admin framework for Rails: you point it at your models and get the resources, filters, and actions without building the back office by hand, and an export becomes an action you drop on a resource. Avo's docs even walk through an [export-to-CSV action](https://docs.avohq.io/3.0/guides/export-to-csv.html) built on the same `CSV` library from this guide, so you can fold in the streaming and sanitizing tricks above where you need them. The build-vs-buy case is not that you couldn't write any of this, you just did. It is that the admin around it, and the years of maintenance, stop being yours to carry. If you would rather spend your time on the product your customers see, [Avo's add-ons](https://avohq.io/addons) cover the back-office features every Rails app eventually needs.

{{cta/}}

## FAQ

**Do I need the csv gem in Ruby 3.4?**
Yes. As of Ruby 3.4.0, `csv` is a bundled gem rather than an always-available default, so add `gem "csv"` to your Gemfile and run `bundle install`. On Ruby 3.3 and earlier `require "csv"` still works without it.

**How do I export CSV without loading everything into memory?**
Two changes. Read records with `find_each` so Active Record loads them in batches of 1000 instead of all at once, and stream the response with `send_stream` (Rails 7.2+) writing one row at a time, so you never build the whole file in memory. In our benchmark this kept a 1M-row export at about 1 MB instead of 76 MB.

**Why does Excel show garbled characters like `JosÃ©`?**
Excel does not assume UTF-8 unless the file starts with a UTF-8 BOM (the bytes `EF BB BF`). Prepend `"\uFEFF"` to the CSV and set `charset=utf-8`, and accented characters render correctly.

**Is CSV export a security risk?**
It can be. If a cell starts with `=`, `+`, `-`, or `@`, spreadsheet software may run it as a formula (CSV injection). Sanitize user-supplied values by prefixing any such cell with a single quote before writing the row.

**How do I handle exports that time out?**
Move the work off the request. Generate the file in a background job, write it to a `Tempfile`, store it with Active Storage, and email the user a download link when it is ready. The request returns immediately and nothing sits open waiting.

Have a good one and happy exporting.

