Parse Excel Sheet in Unity3D

Have an excel sheet containing all the weapons and their parameters for the game? Want to display or use the data contained in a excel sheet? But, how would you access this excel sheet via code? Confused? Let us learn how to parse excel sheet in Unity3D!

The most convenient way to parse an excel sheet in Unity3D is to convert it into CSV.

What is CSV?

CSV stands for “comma-separated values”. CSV is a simple file format used to store tabular data, such as a spreadsheet, excel sheet or database. Files in the CSV format can be imported to and exported from programs that store data in tables.

CSV Parsing in Unity3D

Here is a code snippet that would help you parse your CSV in Unity3D:

using UnityEngine;

public class CSVParse : MonoBehaviour
{
    /// <summary>
    /// The csv file can be dragged throughthe inspector.
    /// </summary>
    public TextAsset csvFile;

    /// <summary>
    /// The grid in which the CSV File would be parsed.
    /// </summary>
    string[,] grid;


    void Start()
    {
        grid = getCSVGrid(csvFile.text);
    }

    /// <summary>
    /// splits a CSV file into a 2D string array
    /// </summary>
    /// <returns> 2 day array of the csv file.</returns>
    /// <param name="csvText">the CSV data as string</param>
    static public string[,] getCSVGrid(string csvText)
    {
        //split the data on split line character
        string[] lines = csvText.Split("\n"[0]);

        // find the max number of columns
        int totalColumns = 0;
        for (int i = 0; i < lines.Length; i++)
        {
            string[] row = lines[i].Split(',');
            totalColumns = Mathf.Max(totalColumns, row.Length);
        }

        // creates new 2D string grid to output to
        string[,] outputGrid = new string[totalColumns + 1, lines.Length + 1];
        for (int y = 0; y < lines.Length; y++)
        {
            string[] row = lines[y].Split(',');
            for (int x = 0; x < row.Length; x++)
            {
                outputGrid[x, y] = row[x];
            }
        }

        return outputGrid;
    }

}

Here, the CSV File data is stored in a two-dimensional array. In order to fetch a specific value at a given index, use the following function:

    /// <summary>
    /// Gets the value from the CSV File at index(row,col).
    /// </summary>
    /// <param name="row">Row.</param>
    /// <param name="col">Col.</param>
    void getValueAtIndex(int row, int col){
        Debug.Log(grid[row, col]);
    }

Similarly, use the following function in order to display the contents of the whole grid and to check whether your data has been correctly parsed:

    /// <summary>
    /// outputs the content of a 2D array.
    /// </summary>
    /// <param name="grid">2D array , here the CSV grid.</param>
    static public void DisplayGrid(string[,] grid)
    {
        string textOutput = "";
        for (int y = 0; y < grid.GetUpperBound(1); y++)
        {
            for (int x = 0; x < grid.GetUpperBound(0); x++)
            {

                textOutput += grid[x, y];
                textOutput += ",";
            }
            textOutput += "\n";
        }
        Debug.Log(textOutput);
    }

Happy Coding!