Seeding Ecto with a CSV file

Questions / Help
1

I want to seed my Users table using a CSV file. I have it reading the file okay, but I don’t know how to typecast what CVS decodes (map?) into my Ecto Model.

File.stream!(Path.expand("priv/repo/users.tsv"))
  |> Stream.drop(1) # Skip header row
  |> CSV.decode(separator: ?\t, headers: [:id, :username, :first_name, :middle_name, :last_name, :email, :position, :last_login_at, :last_request_at, :last_request_url, :active, :inserted_at, :updated_at])
  |> Enum.each(&Repo.insert!/1)

Also, my CSV file was exported from SQL Server, and the null columns have NULL as text. Is there a way to replace with nil in a pipeline. Only certain columns could have NULL, not all of them.

Lastly, I’ve already seen the blog post from Wendy; it seems there’s been breaking changes since it was written.

1 Like
2

Well, I got this to work after a bit:

alias MyProject.Repo
alias MyProject.User

defmodule MyProject.Seeds do

  def row_to_user(row) when is_map(row) do
    %User{
      id: normalize_id(row.id),
      username: normalize_username(row.username),
      first_name: normalize_null(row.first_name),
      middle_name: normalize_null(row.middle_name),
      last_name: normalize_null(row.last_name),
      email: normalize_null(row.email),
      position: normalize_null(row.position),
      last_logon_at: normalize_date(row.last_logon_at),
      last_request_at: normalize_date(row.last_request_at),
      last_request_url: normalize_null(row.last_request_url),
      active: normalize_active(row.active),
      inserted_at: normalize_date(row.inserted_at),
      updated_at: normalize_date(row.updated_at)
    }
  end

  def normalize_id(value) when is_binary(value) do
    {result, _} = Integer.parse(value)
    result
  end

  def normalize_username(value) when is_binary(value) do
    String.downcase(value)
  end

  def normalize_active(value) when is_binary(value) do
    case value do
      "0" -> false
      "1" -> true
    end
  end

  def normalize_date(value) when is_binary(value) do
    case value do
      "NULL" -> nil
      "" -> nil
      _ ->
        parts = String.split(value, " ")
        Ecto.DateTime.cast!("#{Enum.at(parts, 0)}T#{Enum.at(parts, 1)}")
    end
  end

  def normalize_null(value) when is_binary(value) do
    case value do
      "NULL" -> nil
      "" -> nil
      _ -> value
    end
  end

end

# Users
File.stream!(Path.expand("priv/repo/users.tsv"))
  |> Stream.drop(1) # Skip header row
  |> CSV.decode(separator: ?\t, headers: [:id, :username, :first_name, :middle_name, :last_name, :email, :position, :last_logon_at, :last_request_at, :last_request_url, :active, :inserted_at, :updated_at])
  |> Enum.map(fn (row) -> MyProject.Seeds.row_to_user(row) end)
  |> Enum.each(&Repo.insert!/1)
5 Likes