CSV export of Ecto models

This post is about converting Ecto models to a csv file using this CSV library

Ecto is a database DSL for Elixir and is part of the Phoenix framework. Recently we had a requirement to export one of the tables as CSV (you can never escape such export requirement as long as there is excel right?)

Here is an ecto model we wanted to export

  defmodule Message do
    use MyApp.Web, :model

    schema "messages" do
      field :message, :string
      field :status, :string
      field :uuid, Ecto.UUID
      timestamps()
    end
  end

It is a pretty simple model backed by table called “messages” which has the columns message, status and uuid

Get the data to be exported from the database

...
messages = Message order_by(:inserted_at) |> Repo.all
...

And trying to convert it to CSV

messages
  |> CSV.encode
  |> Enum.to_list

does not work because the CSV encoder does not know how to deal with the Message type. So, define an encoder function for the Message type

defmodule Message do
  ...
  defimpl CSV.Encode, for: Message do
    def encode(cm, env \\ []) do
      [cm.message, cm.status, cm.uuid]
        |> Enum.map(fn(v) -> CSV.Encode.encode(v, env) end)
        |> Enum.join(",")
    end
  end  
end

We are creating an array of all the needed column values and encoding them individually. Then, joining them to form the CSV for the given message struct

But this is not enough. CSV.encode/2 expects a stream of data in a tabular format, and encodes it to RFC 4180 compliant CSV lines. By that it means the data should be in the format

[
  ["row1-col1-data","row1-col2-data","row1-col3-data"],
  ["row2-col1-data","row2-col2-data","row2-col3-data"]
]

So, wrap the array of Ecto Message objects into an array of array having a message object per row

[
  [message1],
  [message2]
]

Do that and pass it to the csv encoder

...
messages = Message order_by(:inserted_at) |> Repo.all
  |> Enum.map(fn(m) -> [m] end)
messages
  |> CSV.encode
  |> Enum.to_list
...

The resulting string will be the CSV of Ecto message instances

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s