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.

One Comment

  1. Nidhi June 17, 2015 13:31 Reply

    :) Thank you. You saved me. :) I am going to try this for my game and might bother you if stuck. :p

Leave a Reply