Excel to Unity Pipeline

exceltounity

The purpose of this tool is to enable the fastest testing iteration cycles for your game balancing. You can setup objects and their properties in excel and with 2 clicks you will be able to see or test that change in unity (well, 3 clicks if you count the “play” button in unity :) )

This can be useful for game objects that need a lot of data to operate e.g. Units, Weapons or whole economies or for game objects that need a lot of repitive setups e.g. Cards ins CCGs, or items in a RPGs.

Game Data Handling

Games, on the lowest level are made up of objects with behaviors. So basically it’s things doing stuff. These objects consist of mechanics (what these objects do e.g. run/attack/produce) and of properties (their values e.g. speed/attackpoints/cost).

While the mechanics have to be implemented in code, their properties can be defined in various ways

  • Hard Code them – There are tons of reason why this is a bad idea, and you should never do that
  • Set them in the editor – Many Editors have features with which the designer can create objects and set their properties to then save them as prefab. Depending on what you want to create the unity prefab system might be well suited for you. As you know, you cat write custom editor extensions that can help you create your game Assets.
  • Create configs following XML or JSON formats, these configs will then be loaded during runtime and the code knows what to do with them (create objects, assign their values to game-objects etc.)

This tool will take config approach.

The balancing and relationships of a games objects is usually done in external tools (excel, google-spreadsheets etc) .Hardcoding or creating assets in your editor would require a lot of repetitive and manual work since you would have to basically recreate the data you have in excel in some other place.

Working with configs comes with some advantages

  • They are text!
    • This means they work perfectly with any subversion systems you might be using in your project.
  • Everything is one place
    • You don’t need to check several gameobjects in your scene/resources folder if you want to see the properties that are currently used in your game
  •  It’s Convenient
    • If you have a nice way to create your game objects from code, you don’t need to maintain huge assetlists in your project (which is always prone to human error)
      However, the downside is that it comes with a higher implementation cost to set up parts of your game to be datadriven.

 

The Tool & Pipeline

This data import pipeline will parse CSV files in unity and create data objects with any number of properties. These data-objects can then be accessed by your game-scripts to retrieve the property values. Data will be set up in excel and a script will export all the tabs to the CSV files.

Download
ImportCSV_Assets

The “ImportData”-Folder Goes into your unityproject.

Note: This download already contains a working example.

The rest of this post tells you how you can setup you own project. While you still need the Scripts from the download.

 

Let’s Start!

Export sheets to CSV Macro

First we need to get the data in your sheets into a CSV format. You can off course manually export the CSVs but we want this to be as fast as possible. We will create a macro that will export all sheets to a CSV file.

I found this beauty here (unfortunately I dont remember where):

Public Sub SaveWorksheetsAsCsv()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

CurrentWorkbook = ThisWorkbook.FullName
CurrentFormat = ThisWorkbook.FileFormat
‘ Store current details for the workbook

SaveToDirectory = “D:\Dropbox\GameDev\Tuts\CSVImport\Assets\Resources\”

For Each WS In ThisWorkbook.Worksheets
WS.SaveAs SaveToDirectory & WS.Name, xlCSV
Next

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
Application.DisplayAlerts = True
‘ Temporarily turn alerts off to prevent the user being prompted
‘ about overwriting the original file.

End Sub

 

Setting up the Macro in Excel
  • Copy the script, and create a new VBA macro in excel.
  • To open the Macros overview, press ALT+F11 in excel
  • Go to “INSERT -> MODULE”
  • Paste the script into the texteditor window
  • Change the target directory
    • The target directory should be the RESOURCE folder of your unity project

change path

  • Test the script by clicking the “Run” button in the macro menu (or press F5)
    • You might to resave your excel file as .XLSM to enable macros
  • Check your unity resource folder if the CSV’s are there
  • I recommend setting up a Hotkey to run this script, so your export will be even quicker:
    • In the excel menu go to “VIEW -> MACROS -> VIEW MACROS”
    • Select “SaveWorksheetsAsCsv” and click on OPTIONS
    • Set a hot key
    • Now you can simply export all of your sheets to csvs with one keypress

makro_hotkez

Setting up Unity
  • Download the Scripts
  • Open “ImportData.cs”
  • Add the sheet names of your workbook to the “importNames” arrays

static string[] importNames = new string[]  { "example","items" };

 

 

How to use it

Setting up your Data

You first row should only contain “headers” (will be converted to an datacontainer later)

In the row after that, add Key-Value Pairs. You can also add multiple objects to one sheet:

excel_excample_2

 

 

Getting the Imported Data in your code

I would encourage you to save the data you get in your own variables since the Import-Lookup will always parse the strings into other types, this can potentially get quite CPU heavy depending on the amount of data you have. Look at ImportExampleClass – if everything worked right you should be able to get your data with code like this:


public void LoadMyData(){

ImportedDataContainer my_container = ImportData.GetContainer("example_id");

mInts = my_container.GetData("level_caps").ToIntArray();
mFloats = my_container.GetData("speed_by_level").ToFloatArray();
mString = my_container.GetData("name").ToString();
mFloat = my_container.GetData("reputation").ToFloat();
mInt = my_container.GetData("hit_points").ToInt();
mStrings = my_container.GetData("friends").ToStringArray();

}

 

The class “ImportData” will hold the data from the csv. Retrieve the datacontainer you are looking for by its id (the header in excel) and then access the values (properties) by their keys.
You don’t need to care about actually loading the data. ImportData is a singleton with lazy initialisation. All you need to do is making sure that you have added the filenames to ImportData.

GetContainer(ObjectID).GetData(KeyID).ToType()

access

data_in_unity

 

 

 

 

 

 

 

 

 

 

 

 

 

Data Structure

Key-value pairs are grouped in an object. Each object has an ID which you can later use in your code to retrieve the data from that object. Keys and value in excel are off course Text, and later in unity this data will be stored as a string. However, the tool offers simple conversion methods to read that data as a specific type. Although you need to know what datatype your value _actually_ has.

Supported Types

  • Int
  • Float
  • String
  • Int Array
  • Float Array
  • String Array

To set up an array-value in excel, you need to seperate the values by a “-” (to use a different value you need to change the variable “arraySplitChar” in the ImportedDataPoint Class.

 

Implementation

In case you  care about the implementation, or you need to fix/add something here is an overview of the general Idea:
The table from Excel gets converted into a comma seperated file. “ImportTable” loads this text file as TextAsset and converts this into a 2 Dimensional Array ( string[col, row]). This table is passed on to ImportData where it uses the table-functions to read all the key-value pairs that follow a specific header.
Each “header” will become an “ImportedDataContainer” that holds the “ImportedDataPoints” for this object.

Note: When looking for the “next” header, the search does not look into the last coloumn. Apparently the CSV contains some weird character at the end of a line that will be converted into a coloumn. I couldn’t find a way to work around that via code, so I implemented that hack.

Cat Rats From Space

 

 The Project

promo

Cat Rats from Space was developed within 10 very crunchy weeks. I developed a a prototype to test the basic gameplay and then I moved to unity an started all over again. Shooting by tapping on one side of the screen to hit target, spawning on the opposite side. Enemies would get harder and harder and the players goal was simply to achieve a new high score. The game that I shipped had various enemy types that the player would fight on 6 different stages.

Google-Play-Badge

 

 

Design

Core Mechanic: Shooting

Goal Hierarchy: Get Highscore / Progress, Eliminate Enemies

Obstacles: Enemies ( Different Enemy Types, Bossfights, Increasing Difficulty)

 

 

 Key Take Aways

Optimize Game Data Import

As a Designer, I want the Idea-to-Feedback time to be as short as possible. This basically is the feedbackloop on which a Game Designer operates on. Change the system / tweak a component or mechanic, test it in-game, evaluate the result, come up with the next step, and then repeat. Often these tweaks are done in excel. If I would be having the perfect setup & pipleline it would work like this:

I add a new enemy in excel, give him some HP, a shield with 5 charges and a speed of 3. I press a button, Unity opens up, loading a level with that enemy and I can fight it.

Unfortunately I am not there yet. And actually, in Cat Rats from Space I terribly failed achieving this this. But at least I had some learnings that will help me to get a bit closer in the next iteration:

  • Don’t care about Data-Import in early Prototype mode too early
  • The ingame assets should be simply recreated from the imported data (I had a mixed approach where I would UPDATE or ADD stuff to existing Prefabs and of course that when terribly wrong since the added complexity is prone to errors and problem)
  • Only import data and create assets from code where it is actually needed. Naturally there will be parts of the game which you will iterate more, and some that you only touch a few times, setting up the data import is only necessary for things that are going to change often and that you want to test often. Evaluate first, if the means justify the effort!
Nail Down the Design

Changing direction once you are in production can set the project back quite a bit. So I can not stress this enough. You never want to change the design once you have starting building it. Yes, games are all about iterating, testing and improving. But, the heavy lifting in that regard, should be done in the prototyping phase. This is where you find out into what direction the game is going and define the very core of it. Long Story short. In the middle of working on the game, I switch from procedural enemy wave & level generation to an static one. This did not only affect the balancing and the design, also a huge chunk of the code had to be rewritten.

Tools Tools Tools

Since this was the first game that was actually planning to release – I was faced some unique challenges along the way that I hadn’t encountered previously. To keep iteration cycles as fast as possible it is crucial to develop and maintain tools for an efficient content creation and game-data import. Yes, it takes a lot of time out of the budget, but it will soon pay off. Even in this game, that doesnt haven’t have a huge amount of content I was glad that I  had spent that time.

Post Launch is a full time Job

Not really news to anyone who is working in the business. But, just putting a game out there doesn’t mean that anyone will actually play it. Unless you hope on being as lucky as Dong Nguyen, the creator of Flappy birds, promoting the game after or while launching it, is tough job that requires full attention. And that work should start before the game is finished! The intention of Cat Rats in Space was never to reach a mass audience and generate any money – but by just uploading it to the store and telling friends / families / colleagues and fellow students, the game has got about 50 downloads in 1 year. Of which 30 were organic installs.

 

startscreen shield_mob shield_boss gameplay2