Today I would like to share the idea of converting Excel file to JSON in Unity 3D at run time. Most of us using Excel sheet to store the data for the different purposes, here in Excel there is no direct way to get the information from the sheet to Unity 3D. I often used excel sheets to create a data file for my game levels. It takes more time to convert the Excel file to JSON by manual work, such as online file converter, JSON editors.
Tried a lot of plugins from the Unity Asset Store and Visual Studio C# packages for covert the Excel sheet to the JSON file. Most plugins are working on the Unity Editor window only, which is not useful to do this dynamically at runtime. Here I come up with a solution to do the Excel to JSON conversion.
Parse Excel file to JSON Object
Lets consider a sample excel sheet with some values, I will use the following sheet as a sample in this tutorial. This sheet having the primitive data types for example.
Once the file is ready save the sheet as CSV (Comma Separated Value) file, it is also similar to excel sheet type and you can do further changes without any conversion of CSV – Excel – CSV. CSV file can be opened by any spreadsheet application and in notepad editor.
After the conversion keep the file in Application Streaming Assets folder (I’m doing this for Windows desktop application, in your case this may differ). Make sure that the file can be accessible at runtime of the application.
Now we have to do create some codes to get the data from the CSV file. Since the CSV file can be accessble via notepad editor, let consider it as a regular text file. So get the text data from the file by reading it from the file path. Then separate the values based on the comma delimitar ” , “. By this we can identify the cell values and implement the logic for creating JSON object.
To simplify this process I have found a script to parse the CSV file, this script will read the text input of CSV file and creates array of data based on the row and comma separation.
The above is the script which parse the text data into array of data. Now we have to use this script and send the text data by following method.
How to Read CSV File in Unity 3D
To read the files in Unity 3D get the file path and read it using System.IO. Before that, we have to create a data model class for the excel sheet. It will be used to parse the text into the object. Here is an example.
Use this following to read the file and call the Deserialize() to parse the CSV file, like below.
Well, now we have parsed the excel sheet value and stored it in the model class. You can use the model in your project or you can use save the JSON data into the file for future uses. You can write the data into a JSON file by using the following.
To convert the JSON to string use the JsonUtility, you can find more information about using JsonUtility in this post – Parse JSON in Unity 3D. On successful conversion, the JSON file will be like following.
Hope this tutorial will useful, Happy Coding!
