[Solved] CSV data key lookup

support
1

I know there will be someone with more knowledge on how to do this (efficiently)…
Is it possible to have a key in front matter and look up a CSV to return the row of data referenced by that key and making each snippet of data available?

so far I can load the CSV

{{ $CSV := getCSV "," "data/abc.csv" }}

output the whole range that is on the CSV…

{{ range $CSV }}<br>
{{ . }}
{{ end }}

and find a couple of random rows and output them…

{{ $CSV | shuffle | first 2 }}

however - what i hope to do… with front matter like this example…

+++
title = “some random page title”
id = “111”
+++

And CSV like this example…

id, description, color, number, cat, image
110, blue pearl, blue, 10, pearls, bpearl.jpg
111, green pearl, green, 12, pearls, gpearl.jpg
112, orange peel, orange, 19, fruit, opeel.jpg

→ lookup the csv with something like…

{{ range $r := $CSV where "id" == .Params.id }} (I know this is not correct)

to find the id the same as the one in the front matter, load that row, make available each aspect of data, and use the data on my page…

 <div class = "{{ $r.cat }}">
 <span> {{ $r.id }}</span>
 <span> {{ $r.description }}</span>
 <span> {{ $r.color }}</span>
 <span> {{ $r.number }}</span>
 </div>
 <img src = "{{ $r.image }}>

I dont know enough and have spent a lot of time on docs / here / Go / stack exchange and nothing that works to date.
this is the closest I have found - Range over getCSV array not iterating - #3 by O_O but no joy using it
Thanks in advance…

2 
{{ range $i, $r := getCSV "," "data/abc.csv" }}
{{ if eq (index $r 0) $.Params.id }}
      {{ index $r 5 }}
{{end}}
{{end}}

In the middle where I have {{index $r 5}} you can add individual entries, or just range over $r.

Then you can even filter by ID (although ID is unique so you would need to filter by something that has multiple entries first), then filter by another column by adding another if eq filter within the params.id one. Quite powerful, and I am building some really sophisticated functionality myself using google sheets to manage data (you can publish a csv to the web from google sheets, that you can use as the csv url).

An example of all data in a table (using range $r) is as follows:

        <table border="1">
   
        {{ range $i, $r := getCSV "," "data/abc.csv" }}
        <thead>
        {{ if eq $i 0 }}
        {{ range $r }}
        <th>{{ . }}</th>
        {{end}}
        {{end}}
        </thead>
        {{ if ge $i 1 }}
        <tr>
            {{ range  $r }}
            <td>{{ . }}</td>
            {{end}}
        </tr>
        {{end}}
        {{ end }}
    </table>

Hopefully these two examples will let you see what is possible.

1 Like
3

@yeehaa, I had similar troubles using getCSV as you found my post in your search, Range over getCSV array not iterating. I’m sorry if my explanation didn’t help you there.

To follow up with what I learned, it seems that the getCSV function reads the .csv file into an array, with each segment split by the character delimiter you specify, in your example the comma is used "," as the delimiter. this array can be ranged over, but in a csv file like the example you gave, it seems you want to first split the csv file up by row. Rows are not separated by commas, so you will first need to separate by carriage return which is "\r".

{{ range $i, $r := getCSV "\r" "/abc.csv" }}
{{ end }}

After separating the csv into the array you created (containing row data), you will then need to split them up into each column of that row to find the data you want to match (individual cell data from the first column of the row) The data separated by rows is in the array as a string, so it can be divided using the split function with the comma "," as the delimiter.

{{ range $i, $r := getCSV "\r" "/abc.csv" }}
{{ $c := split (index . 0) "," }} 
{{ end }}

This will then get you access to the data read from each cell of that row using index to get to the substrings made by split. From there you can check to see if the data in the first column (with the index key 0 of the slice) has the data that matches your key variable (in the front matter) for that row in the range. To do this simply we can use a conditional statement to see if it is equal, and if it is not, continue to the next row.

{{ range $i, $r := getCSV "\r" "/abc.csv" }}
{{ $c := split (index . 0) "," }} 
{{ if eq $.Params.id (index $c 0) }}
{{ end }}
{{ end }}

Once the row is found you will want to output the contents of each cell from that row to your page, so you can use the html from your example to do that, within the range, after the split as shown below:

{{ range $i, $r := getCSV "\r" "/abc.csv" }}
{{ $c := split (index . 0) "," }} 
{{ if eq $.Params.id (index $c 0) }}
 <div class = "{{ index $c 4 }}">
 <span> {{ index $c 0 }}</span>
 <span> {{ index $c 1 }}</span>
 <span> {{ index $c 2 }}</span>
 <span> {{ index $c 3 }}</span>
 </div>
 <img src = "{{ index $c 5}}">
{{ end }}
{{ end }}

I hope that this helps you solve the problem that you are having, or at least help you understand what I learned more completely so you can figure out the solution.

4

The examples in the gohugo docs, and the implementation on my own site does not require this when iterating over a csv.

This should work fine:

{{ range $i, $r := getCSV "," "data/abc.csv" }}
{{ if eq (index $r 0) $.Params.id }}
 <div class = "{{ index $r 4 }}">
 <span> {{ index $r 0 }}</span>
 <span> {{ index $r 1 }}</span>
 <span> {{ index $r 2 }}</span>
 <span> {{ index $r 3 }}</span>
 </div>
 <img src = "{{ index $r 5}}">
{{end}}
{{end}}

Also note .Params.id should be $.Params.id

1 Like
5

@Jonathan_Griffin Thanks for catching the $.Params.id mistake. I had issues getting the csv file to separate row information like the examples in the docs.

When I followed code like your example, it would repeat the same data at the beginning of the csv file instead of just data from that row. I’m not sure if there was something I did wrong, but hopefully @yeehaa can let us know which implementation works for @yeehaa’s use.

I’m glad you were able to implement it on your own site without problems, I felt that the documentation on getCSV was a little too sparse for my understanding, which is why I had to use trial and error with it until I figured out how to get it to work for my use case.

6

Beautiful… thanks for the fast response, and the concise info.
Would love to know more about your project.

I havent learnt how I can mark this as -solved-? [Solved]

7

@O_O thanks for your response.
I have gone with…

{{ range $i, $r := getCSV "," "data/abc.csv" }}
{{ if eq (index $r 0) $.Params.id }}
<div class = "{{ index $r 4 }}">
<span> {{ index $r 0 }}</span>
<span> {{ index $r 1 }}</span>
<span> {{ index $r 2 }}</span>
<span> {{ index $r 3 }}</span>
</div>
<img src = "{{ index $r 5}}">
{{end}}
{{end}}

it appears fairly efficient which is what I was after - @Jonathan_Griffin

Both of you have helped me understand more and I agree…

I felt that the documentation on getCSV was a little too sparse for my understanding, which is why I had to use trial and error with it until I figured out how to get it to work for my use case.

The learning curve is in fact a loop :dizzy:

8

I am revamping quite a large website that has lots of functionality that would typically have required databases.

There are two main features that will be using csvs:

  • Review system - This will use Typeform linked to Google Sheets, which is then iterated on via the published csv. (I have created some code snippets for this, but it is not yet implemented (scheduled for approx. 2 months time))
  • Coupon system - This is implemented, and uses similar code snippet to what I posted above, except I first iterate through the company name (each coupon for a specific company has a unique ID), and then do another iteration for various things (featured coupons, coupon type, other filters etc). Because there are many hundreds of coupons it needed an easy to use system of updating. Google Sheets is very easy, whereas TOML or YAML is not. I have surpassed what my own coding knowledge even thought possible with this. It is so much easier to manage than my existing Django implementation, and looks really great.

Unfortunately, the new project will be launched with a lot more content, and better UI, so it is not simply a matter of coding something for the existing content. I anticipate a launch between 6 and 8 months time. It is a major project. both in terms of new code, but probably the majority of time is for new content.

Also, I use Zurb Foundation, and created my own theme from scratch.

9

@Jonathan_Griffin and @yeehaa , I figured out that the issue I was having was related to the way that the .csv file was generated when saving from an excel spreadsheet for mac. It seems that office on mac didn’t follow the same conventions as windows or the official spec required by hugo for csv data files.

1 Like