Working with spreadsheets of data


#1

How do you work with spreadsheet like data with Kirby? One way is to use a structure field, but that is not as readable as a spreadsheet.

One alternative is to use some plugin like https://github.com/floriankarsten/kirby-tablex

CSV editor

I always do things on localhost so I can use the panel, a code editor or anything else to shape my content. For spreadsheet like data, I’ve now tested to use a CSV editor:

https://www.ronsplace.eu/products/ronseditor

In my opinion it has an ugly UI but it’s working really well for Windows. If you don’t like it, there are more CSV editors out there. Just be sure to test that åäö or ü works without any questions on load or save.

Content

I place the CSV file in the content/my-page folder.

Read

In a controller or a page method I do something like this:

$filepath = $page->root() . '/spreadsheet.csv';
if(file_exists($filepath)) {
  $string = f::read($filepath);
  $out = explode("\n", $string);
  array_shift($out); // Remove headings
  foreach($out as $i => $item) {
      $split = str_getcsv($item);
      // Do something
  }
}

A controller can looks like this, where data.php includes the code above:

<?php
return function($site, $pages, $page) {
  return [
    'data' => include __DIR__ . '/data.php'
  ];
};

Pros and cons

Pros

  • We work with the files offline and the file system is very safe, like the data is always saved correctly.
  • We are not getting logged out or anything else that can happend during being on a server.
  • CSV is a common format so we can use any CSV editor out there that we like.

Cons

  • The data is no longer tied to the page object, at least not in the same way as before. We can however still use a page method.
  • There is one or more extra files in every page folder. In theory it can be slower. At the same time CSV is faster than YAML to parse.
  • We no longer edit all the things in the panel which can be a downside, expecially for clients that want to go to a single location.

In conclusion

As great as the panel may be, I always want to learn new ways of doing things. There are so many ways we can work with Kirby. We can edit the content text files manually, or not use them at all, like in this case. We can use CSV applications to do our spreadsheets. We can use markdown editors to write our page content if we want (I like https://typora.io right now). So many possibilities.