Elixir/Phoenix Project: Import CSV & Seed Data
Tags: elixir, tutorial, sideprojects
Published: 2020-12-01 15:32:27

#### Goals
  - Seed the `destinations` table

I finished [setting up the first schema](https://dev.to/noelworden/elixir-phoenix-project-create-first-schema-run-tests-in-docker-5979) with everything needed to CRUD (create / edit / update / delete) a record in the `destinations` table. The views and forms are functional, but they definitely need some love, particularly the `index` view. But before I dive into any styling, it would be beneficial to get some data into the `index` table. I could have used the app's newly created html forms and generated the data one record at a time, but its far more efficient -and beneficial down the road- to utilize the `seeds.exs` file. Using this method, the database can be populated quickly and consistently.

#### Seed the `destinations` table

Phoenix ships with a seed file, located at:

`priv/repo/seeds.exs` 

Functions in the seed file don't have to be complex, it's simply an automated way to insert data into the table. A lot of times the data being inserted is fake, and primarily used as a kind of placeholder. But in my case I already had a spreadsheet with all the data and figured that since I was going through this process to inject data, it might as well be real data. Taking the time to write functionality to ingest data from a spreadsheet would also allow me to batch upload data through the browser at some point, which could be handy. Lastly, this kind of spreadsheet ingestion was handled in my last work project, so I had some experience with it, as well as some existing functions to guide me through the process. 

Because of the potential dual use of the ingestion code I would be writing, I created a new `CSVUtil.ex` file, and housed it under the `Mapping` context. I did a bit of digging to see what libraries were available for processing a CSV (that's the format I exported the spreadsheet to for ingestion), and [NimbleCSV](https://hexdocs.pm/nimble_csv/NimbleCSV.html) was the clear front runner. I wasn't surprised, as that's what we used on my work project. 

Knowing that I would use the same library as I already had experience with, I took an iterative approach to writing the functionality. Basically, what happened over the course of a day was:
  1. Starting with the work project functions as a rough scaffold, I implemented my own version of CSV ingestion.
  2. My initial version was a little verbose and clunky, but functional.
  3. I started refactoring my work and seeing how and where it was similar to the work project code.
  4. Ultimately I ended up with a good bit of similarity to the work project code, but with a much better understanding of what was happening from start to finish.

I will be discussing the end result of that process shortly. But first things first, the `NimbleCSV` library needed to be added to the dependencies in `mix.exs`:

{% gist https://gist.github.com/noelworden/a76b7b536d3b52a7c946edc6f9fdb9e5 %}

Then I fetched the new dependency with:

`mix deps.get`

Once that finished, I rebuilt the docker image:

`docker-compose build`

And, finally, spun it up again:

`docker-compose up`

With the library added to the project, I added an alias of the implementation of `NimbleCSV` I planned on using in `CSVUtil.ex`:

{% gist https://gist.github.com/noelworden/c5101e6cda1b0ba61b6c752545232a9b %}

This allows me to just call `CSV.<function>` instead of `NimbleCSV.RFC4180.<function>` every time.

Pulling from my previous experience, I knew I needed to either hardcode the CSV headers or grab them from the file. Initially I hardcoded them, but for future versatility I went with this function that extracts the headers and maps them to an index:

{% gist https://gist.github.com/noelworden/dcc2209030041b1e5ddcdc154cdf2811 %}

The breakdown of that function:

{% gist https://gist.github.com/noelworden/ea86000cd5f51b558a20b3c7c3cced8d %}

This takes in the CSV file and streams it line by line, which is perfect creating a record from each row of the CSV. This function alone only returns a struct with data about the file, its best utilized when another function is piped into it.

{% gist https://gist.github.com/noelworden/4f2e62ebac293e77a48de94a2f5e87df %}

This tells `File.stream!()` to break that file down line by line, then parse it into a stream of rows. The default action of `parse_stream` is to exclude the headers, so I need to explicitly state that I want to include the headers here. The rows aren't user-facing until they are looped over.

{% gist https://gist.github.com/noelworden/fedf46bb467da0dcc75af44a77c2e58f %}

This is returning only the first row of the stream, and dropping the rest. At this point the results start to take shape into something usable:

{% gist https://gist.github.com/noelworden/c4bc43f3a3ce586d63be8deb52198665 %}

Next is:

{% gist https://gist.github.com/noelworden/4ab8c77149a46542ebec8f60e003789a %}

This takes that list of strings and creates a list of tuples, the string as the first element and its index as the other, like this:

{% gist https://gist.github.com/noelworden/186160d4dec87971137249fef7086bbc %}
 
And lastly:

{% gist https://gist.github.com/noelworden/e1bf279bde89f55e9d5e4868bbe4b858 %}

This converts the list of tuples into a map, using the index value as the key and the string as the value:

{% gist https://gist.github.com/noelworden/19f2a5168537779006d4f4ca17feac6d %}

The need for the indexing and conversion to key/value pairs will reveal itself in the next step.

The next function, `csv_row_to_table_record/1` calls the first, sets it as a variable, and then works through the rest of the CSV file, to insert it into the `destinations` table:

{% gist https://gist.github.com/noelworden/68daa4da50c3d518577ff3a6685515f6 %}

I'll skip the first two pipes, since they were covered above, and start with:

{% gist https://gist.github.com/noelworden/a1eddbe6178a0230ecc3262c215f0fb9 %}

That is taking each row, and adding an index to each field in that row. A small sample of the output looks like this:

{% gist https://gist.github.com/noelworden/3dce80abe4482782a271c4980bc31ce3 %}

Then there is:

{% gist https://gist.github.com/noelworden/b66f996db35a36456cd140b6201f9656 %}

Which is taking each of those rows, creating a new map from it, and using the indexing to match the header value from `column_names` to the row value, the result of which is:

{% gist https://gist.github.com/noelworden/3940c8128d91a3d7ef063e874f1b5fdc %}

And, finally, there is a custom function to use that map to insert the data into the table:

{% gist https://gist.github.com/noelworden/43ed147ff88ba866c4b1dd3539b54fb5 %}

The goal with this function was to be able to update that CSV, execute the seeding file, and only have the new files be added to the database, so the standard create function:

{% gist https://gist.github.com/noelworden/e6a9ca95dcae271c07dd1dd412abff64 %}

Wouldn't work here, because it would add the entirety of the CSV to the table each time, instead of only the new records. This is what the custom function looks like:

{% gist https://gist.github.com/noelworden/58fc85f8ca5e1e9cf3b8727a58f79f41 %}

The argument `row` is the map created in the pipe above the calling of this function. The `case` is searching the `destination` table for the lat/long coordinates created in the call above. If that search returns `nil`, the map and `Mapping.create_destination` are utilized to create and insert a new record. Because of the header/value pairing that happened in the creation of the map, I can reference the value of the fields with this syntax:

{% gist https://gist.github.com/noelworden/e8c947b45e4233aa9012bd66b54c75bb %}

If those coordinates do exist, it will return a tuple instead of creating a new record.

This logic might be easier to parse when seeing everything at one time, here is the file in its entirety:

{% gist https://gist.github.com/noelworden/a6665a100373c7d36f95db95fc7bd81c %}

The spreadsheet I've been using to hold my data up to this point is a Google Sheet. I exported it out as a CSV, and created a new folder at:

`priv/repo/data`

To store this file an any potential future files of this nature.

With all that in place, the code in `seeds.exs` is actually only one line:

{% gist https://gist.github.com/noelworden/8beace2f32a1c811fe89610c51d08255 %}

And, as the notes in the seed file mention, that can be executing by running:

{% gist https://gist.github.com/noelworden/3f4fcc7772c416c5c8ad39ff0fe11328 %}

#### Closing Thoughts

This was definitely a more complex data seeding than is typically performed as the first seed of a new project. It would have taken a good amount of time to manually create dummy records, and if I ever needed to drop the database and rebuild it, I would have had to manually insert records again. Instead I took a bit more time and created reusable logic that can seed the database with actual records in quite literally the blink of an eye. I also now have the foundation to do more work with CSVs, which seems like a feasible option for mass uploading in the future.  

As always, here is my [repo](https://github.com/noelworden/atlas/tree/1e41d51f5e2b89e955343f20cbb544e78dba4e0f) at the end of this block of work. 

#### References
- https://elixircasts.io/seeding-data-in-phoenix
- https://hexdocs.pm/nimble_csv/NimbleCSV.html
- https://hexdocs.pm/elixir/1.3.0/File.html#stream!/3
- https://joyofelixir.com/11-files/

------

This post is part of an ongoing [series](https://dev.to/noelworden/new-side-project-new-blog-series-135h). I encourage any critique, feedback, or suggestions in the comments.