Elixir: Spreadsheet upload / CSV parsing

Uploading CSV in Elixir

Hi everyone! I’ve been looking for ways to upload multiple data in my Elixir application using CSV file. Now that I learn to, I want to share with you guys my way of doing it…

There are lots of ways you can do in uploading CSV files in Elixir. I hope this will help you do it. If you find something wrong, odd, or let’s say you have any comments… just tap me here, I’m open to learnings. Let’s get started!!

Setup

First, make a phoenix project. I named my project Cars. https://hexdocs.pm/phoenix/up_and_running.html#content

mix phx.new cars

After this, let’s generate our schema, context & controller.

mix phx.gen.html Auto Car cars name:string model:string brand:string

In our mix.exs, put {:csv, “~> 2.3”} in deps. Then run mix deps.get.

https://hexdocs.pm/csv/CSV.html

defp deps do
...
 {:csv, "~> 2.3"}
...
end

Controller

Assuming that we already updated our dependencies… We now add resources in the router for our CRUD and add the POST method for our importing of CSV.

resources “/cars”, CarController
post “/cars-import”, CarController, :import  # <--- post method

In our car_controller.ex

Let’s create a new method of import.

def import(conn, %{"csv" => csv}) do
    data = csv_decoder(csv)
    result = import_cars(data)
  conn
    |> put_flash(:info, "Car imported successfully.")
    |> redirect(to: Routes.car_path(conn, :index))
end
defp import_cars(data) do
    cars = Enum.map(data, fn {:ok, car} -> parse(car) end)
    params = Auto.convert_params(cars)
    {_, _} = Auto.insert_cars(params)
end

Inside this block, we need to create a method for our csv_decoder and import_data for the importing of data using the CSV file.

defp csv_decoder(file) do
    csv =
      "#{file.path}"
      |> Path.expand(__DIR__)
      |> File.stream!()
      |> CSV.decode(headers: true)
      |> Enum.map(fn data -> data end)
  end
defp import_cars(data) do
    cars = Enum.map(data, fn {:ok, car} -> parse(car) end)
params = Auto.convert_params(cars)
{_, _} = Auto.insert_cars(params)
  end
defp parse(car) do
    fields = Auto.parse_fields(car)
end
#=== INSERT ALL ===== 
def insert_cars(items) do
    Car
    |> Repo.insert_all(items,
      on_conflict: :nothing,
      returning: true
    )
end

https://hexdocs.pm/ecto/Ecto.Repo.html#c:insert_all/3

In my context -> Cars.Auto (lib/cars/auto.ex)

...
def convert_params(data) do
    data
    |> Enum.map(fn cars ->
      cars
      |> Enum.map(fn car -> car end)
      |> Enum.map(fn {key, value} -> {key, value} end)
      |> Enum.into(%{})
      |> convert()
    end)
end
def convert(data) do
    for {key, val} <- data, into: %{}, do: {String.to_atom(key), val}
end
def parse_fields(car) do
    timestamp =
      NaiveDateTime.utc_now()
      |> NaiveDateTime.truncate(:second)
  car
    |> Map.put("inserted_at", timestamp)
    |> Map.put("updated_at", timestamp)
end
...
# CSV DECODER ---->
defp csv_decoder(file) do
 csv =
 “#{file.path}”
 |> Path.expand(__DIR__)
 |> File.stream!()
 |> CSV.decode(headers: true)
 |> Enum.map(fn data -> data end)
 end
# IMPORT CARS ---->
defp import_cars(data) do
 cars = Enum.map(data, fn {:ok, car} -> parse(car) end)
 params = Auto.convert_params(cars)
 {_, _} = Auto.insert_cars(params)
end

On our new page, I put a form where we can upload batch items using CSV. I disregard the normal create form. You can leave it anyway then copy the code above and paste it anywhere you like on the create page.

<h1>Upload file</h1>
<%= form_for @conn, Routes.car_path(@conn, :import), [multipart: true], fn f -> %>
  <%= file_input f, :csv, accept: ".csv", required: true %>
  <div>
    <%= submit "Import cars" %>
  </div>
<% end %>
<span><%= link "Back", to: Routes.car_path(@conn, :index) %></span>

Once you’re done you can now download your CSV file then upload it in our CSV decoder….

Happy coding!!