From Excel to TSV to Rust

I'm trying to build a simple card game, but that's not even the horrifying part of this story. The horrifying part is that I'm keeping my data in Microsoft Excel.

It's perfect! It flattens the data into something that can be more easily expressed as an object, and the relationships between cards are easy to model. Should one card always be 20% more expensive than another, or half as expensive? Easy solution: use an Excel formula. Spreadsheets were built for this. But reading a spreadsheet from a game is madness.

Initially I exported the spreadsheet as Tab-separated Values (TSV), a simple format that obeys CSV rules and Excel can export natively. This happily reduced the problem to reading TSV, but as I wrote a Rust parser for reading that TSV there were enough unwraps and error handling occurring that I decided it would be easier to move all this error-prone part further back in my asset pipeline.

So I decided to turn TSV directly into a .rs file. This is the madness, the horrifying part which is why someone ought to take my keyboard away.

My go-to language for hacky things is Ruby, so I wrote a small Ruby script which can read the TSV and then echo it back out using an ERB template to turn it into Rust code.

# tsv2rs.rb
#!ruby

require 'csv'
require 'erb'

TSV='ARCOMAGE_CARDS.txt'
RST='src/cards.rs.erb'
RS ='src/cards.rs'

def value_or_default(value, default)
  if value == nil or value.empty?; default; else value; end
end

File.new(RS, File::CREAT|File::TRUNC|File::RDWR, 0644)
    .write ERB.new(File.read(RST)).result(binding)

It's very simple, aside from the helper function value_or_default it's just two lines: open a file, then write out the result.

The ERB template is where frightening things happen, where we write code which writes code. But it's not that scary, actually. I want something static, but also in a Vec, so lazy_static showed up. After that it's very simple Rust constructors.

use lazy_static::lazy_static;
use crate::card::*;

lazy_static! {
  pub static ref CARDS: Vec<Card> = vec![<%
  first = true
  CSV.foreach(TSV, col_sep: "\t") do |row|
    # skips header rows, assumes your first column is always a number
    next unless row.first.match? /^[0-9]+$/
  %>
  <% unless first %>,<% end %>
  <% first = false %>
  Card {
    atlas_row: <%= row[0] %>,
    atlas_col: <%= row[1] %>,
    category: Category::<%= row[2].capitalize %>,
    card_name: "<%= row[3] %>".to_owned(),
    cost: PlayCost {
      bricks: <%= value_or_default row[4], 0 %>,
      ...
    }
    ...
  }
  <% end %>
  ];
}

There's some complexity in keeping track of the first time the loop has been iterated over so a comma can be printed between items, but otherwise it's plain ERB that shouldn't look terribly unfamiliar to anyone who's used Ruby on Rails before. Or Jinja with Python.

I highly suggest running cargo fmt afterward, because ERB isn't well known for cleaning up whitespace effectively.

Following this, it was time to glue everything together. For this I found that PowerShell worked marvelously. I found a StackOverflow answer which provided the basics of a script which can save Excel files as other formats, then the Microsoft documentation provided the right format code for the TSV I want. Finally, the command runs the Ruby script from earlier.

# xlsx2tsv2rs.ps1
$ExcelFile = Join-Path $(Get-Location) ARCOMAGE_CARDS.xlsx
$TsvFile = Join-Path $(Get-Location) ARCOMAGE_CARDS.tsv
$TsvFmtCode = -4158
$Excel = New-Object -Com Excel.Application
$Excel.DisplayAlerts=$False
$WorkBook = $Excel.Workbooks.Open($ExcelFile)
$WorkBook.SaveAs($TsvFile, $TsvFmtCode)
$Excel.quit()
ruby tsv2rs.rb

The natural next step for this is to use a build.rs file to invoke this automatically when cargo build is run, however, there's a reason why I don't think that's a good idea: it destroys cross-platform compatibility. The way it is now, I can rebuild my static objects when I'm on a Windows PC which has PowerShell, Excel, and Ruby. While PowerShell is now cross-platform, the only other native platform for Excel is Mac. I'm not even sure if PowerShell on Mac would interface with Excel for Mac the way it has on Windows.

And that's how I learned to convert Excel to Rust.