When you generate a Phoenix project, there is a
priv/repo/seeds.exs file that you can use to seed the database. Let’s see how to seed it from a CSV file.
We’ll be working with the
transactions.csv file from Mint, and the project name is
Minty. This assumes
phoenix.gen.html has been used to create a model and the resources line has been added to
First, consult the documentation for the CSV library and add the dependency to mix.exs:
priv/repo/seeds.exs we’ll use the CSV library to generate a Map for each row of the CSV file with keys that EXACTLY match our Ecto model.
Note that we are dropping the first line of the CSV file. It contains headers, but we need to define different ones that match the Ecto model to make insertion easier.
As the comments indicate, you can run this with
mix run priv/repo/seeds.exs.
This works, but it assumes everything is a string. To match up the types we need to do a bit more work.
The date column in the Mint.com CSV file is unfortunately in M[M]/DD/YYYY format. Additionally, the days are zero-padded, but not the months. (?!!) Ecto.Date can work with several formats, but that is not one of them. Let’s flip this around to be YYYY-MM-DD instead.
This pattern matches on the one- and two-digit months and then rearranges the bits with
-’s in between, also zero-padding the one-digit months.
An earlier version of this used String.split and String.rjust, but I think I like it better with the pattern matching.
Now the migration can have
add :date, :date and the model can have
field :date, Ecto.Date.
All of the amounts in the CSV are positive numbers, and there is a separate transaction_type field that contains either “debit” or “credit”. Mint doesn’t really do double-entry bookeeping so I have no idea why they use this concept. Let’s keep income as positive numbers and change all the expenses (“debits”) to negative.
We’re still working with Strings at this point, so all we need to do is prepend a dash to indicate that it’s negative.
This pattern matches on a Map containing a key of
transaction_type, and either updates the value under the
:amount key, (or not.)
Now the amount field can be :float in both the migration and the model.
Drop the database, re-create it, and run the (edited) migration again:
Now you should have data with a proper date field and positive/negative numbers so that math will work.
Now that the data is loaded, let’s try some queries. I have this in
Play with it in
iex -S mix:
We’ve seen how to seed the database from a CSV file in a Phoenix project including date and float type fields, and some simple queries.
The code for this example is available at https://github.com/wsmoak/minty and is MIT licensed.