What can I do with export/import?

1. Export/import data to/from GoogleSheets or Excel in Unity Editor (during game development)
2. Export/import data from/to Excel file at runtime (from builds, PC/Mac/Linux only) with this plugin. Optional Excel file monitor, which auto-import data on file change, is also available.
3. Excel file monitor, which auto-import excel data in Unity Editor, can be downloaded here
4. Update database data on players devices at runtime from GoogleSheets with LiveUpdate plugin
5. Currently, there is no way to write to GoogleSheets at runtime from players devices (Why not?)
6. Backup/restore database in json format in Editor

Description

You can edit database data in external tools, such as Excel/Open Office/Libre Office or Google Sheets.

BG Database supports export/import to/from Excel files (xls and xlsx), Google Sheets and json (backup only).

Full import/export works in Editor only, but you can update database data on players devices from GoogleSheets with LiveUpdate addon. No way to update GoogleSheets data from players devices yet.

Use json export/import to create full database backup.

This article describes common procedure and ideas, common for both Excel and Google Sheets. (click to view additional guides for Excel/OpenOffice and GoogleSheets)

Data layout

Here is the illustration about how BGDatabase data is mapped to spreadsheets.

Setup guide for export/import

1. Create a datasource for Excel/GoogleSheets
2. Create a job
3. [Optional] Choose tables/fields you want to export/import
4. Export/import

Click for detailed guides for Excel and GoogleSheets

Export does not require any additional steps, but if you want to import data from external datasource for the first time, you need to ensure the data has a proper layout (as described in "Data Layout" section), e.g. your sheets has the same name as your database tables and the first row has field names.

[Optionally] Also, if you want to create a link between your external data rows and database rows, you need to add _id column to your data and ensure a job's "Update ids on import" parameter is toggled on.

Using merge settings

You have full control over which data you want to synchronize: you can add/delete/update and choose which fields you wish to update.

Here is how it works: Every synchronization has a source and a destination. If you are exporting data from BDDatabase to, let's say, Excel file - the source is BGDatabase and a destination- is Excel file. Here is how data is classified during syncing:

So, there are 3 types of rows during syncing:

1. Matching rows : they exist in the Source and in the Destination. Can be updated, and you can choose which fields to update.
2. Missing rows : they exist only in the Source. Can be added to Destination.
3. Orphaned rows : they exist only in the Destination. Can be removed from Destination.

Let's look at merge settings and find out how we can define actions to take during syncing

Using Data Extraction Wizard to import structure(table/fields)

Our import job currently does not change database structure, it does not create missing tables/fields, it only transfers rows. Export job creates missing sheets/fields(headers) in Excel file/Google spreadsheet, but import job does not.

To transfer new tables/fields to the database, you can use interactive "Data Extraction Wizard" if you have the proper data layout (i.e. the first row is the fields names, as described in Data layout section)

1. Save database before running the wizard
2. Select your data source and click on "Data extraction wizard"
   
3. Click on "Read data", review the result, edit names/types if needed and click on "Import"
   
4. Close the wizard and review the results under "Database" or "Configuration" tab to make sure tables/fields were properly created. Save database if modifications were correctly applied or revert the changes (by pressing on "Reload" button in the top left) if something went wrong.

Names mapping config (optional)

By default, naming convention is used to map sheets to tables and columns to fields. It can be overridden with names mapping config, which allows to use any names for sheets and columns and map them to tables/fields. Names mapping config is available for every data source and is used while importing/exporting from Unity Editor

Rows mapping config (optional)

By default, we use _id column in the sheet to keep the link between database row and excel/GoogleSheets row, and we recommend to use this default approach. However, additional approaches are also available: you can use int/string field as row's id if you have such columns with unique values or rows indexes (e.g. 1st database row is mapped to 1st sheet row etc.). Use these alternative strategies only if you can not use additional _id column. Rows mapping config is available at datasource page.

References config (optional)

References config allows to override how relational fields values are serialized. The recommended(default) option is by using row's ID. Additional options are by any int/string field as ID. The requirements are: the values for such fields should be unique and not empty - so these values could be used to identify the row. The limitations: if relational field allows multiple values - the vertical bar symbol (|) should not be used in field values, because it's used as values separator

Additional parameters

Name	Description
Update Ids on import	If you are adding new rows in external tools, leave _id column empty and enable this parameter. ID values from database will be transferred to _id column when you import new rows.
Transfer rows order	Rows order from the source will be transferred to the destination. Import/export and Excel/GoogleSheets are all supported.

Running Export/Import for the first time

To be able to sync with BGDatabase, you need a corresponding structure created in Excel/GoogleSheets, e.g. you need sheets, named after your tables names and header columns, named after you field names (as described above in "Data Layout" section).

You can easily create this structure by exporting your data to the empty spreadsheet/xls file

• Make sure to target empty spreadsheet/xls file
• Make sure to include all tables/fields you want to export in the settings. Included tables/fields will be highlighted in green
• Make sure all tables you want to include have "Add missing rows" toggle turned on.(cause all the rows will be "missing rows" when you export to empty spreadsheet)
• Export data once - all required structure will be created for you

Row-level control for merge settings

Merge settings let you configure which table/fields you want to add/delete/update, but it does not give you the opportunity to define which rows you want to add/delete/update

For row-level control, you can implement C# controller class, which can cancel any operation if some condition is met. This is how:

• Create your own C# class with unique name, set this class name to "Controller Type" field of merge settings (using the full name with namespace).
• Implement any number of interfaces (namespace is BansheeGz.BGDatabase, interfaces defined inside class BGMergeSettingsEntity), listed below to receive callback method invocations. Return true from these methods to cancel operation

Interface type	method(s) to implement	description
IMergeReceiver	public bool OnBeforeMerge(BGRepo from, BGRepo to) public void OnAfterMerge(BGRepo @from, BGRepo to)	Callbacks to be called before after merging. Return true from OnBeforeMerge to cancel merging
IRemoveOrphanedReceiver	public bool OnBeforeDelete(BGEntity toEntity)	Callbacks to be called before deleting the row. Return true to cancel removal.
IAddMissingReceiver	public bool OnBeforeAdd(BGEntity fromEntity)	Callbacks to be called before adding the row. Return true to cancel adding.
IUpdateMatchingReceiver	public bool OnBeforeUpdate(BGEntity @from, BGEntity to)	Callbacks to be called before updating the row. Return true to cancel updating.
IUpdateMatchingFieldReceiver	public bool OnBeforeFieldUpdate(BGField fromField, BGField toField, BGEntity @from, BGEntity to)	Callbacks to be called before updating the row' field. The same as IUpdateMatchingReceiver, but it's called on per field basis. IUpdateMatchingReceiver is called before this callback, and if IUpdateMatchingReceiver return true, this callback will never be invoked (cause updating entity will be cancelled by IUpdateMatchingReceiver)
ISaveLoadAddonSavedEntityFilter	bool OnSaveEntity(BGEntity entity)	Callback is called by SaveLoad addon while saving the data for each row. If this method returns true, the row is skipped and not included into saved data

You can find an example of such controller class on Save/Load addon page.

Default field value string format

We use Acute (Back quote) symbol ` to separate tokens of single value, for example Vector3.zero is 0`0`0

We use Vertical bar (Pipe) symbol | to separate values of the list, for example List<bool> is 0|1|0

Custom field value string format

Most of the fields support custom string format (except for Unity assets and relations). You can create your own class, implementing BGStringFormatter<T> interface (T is a field value type) and assign it under "M" menu as a custom string value formatter

Adding rows in external tools

Each row has its own id- 22 symbols string. It's computer-generated value, based on Guid generation.

While creating rows in external tool, you can leave it blank, and it will be updated during importing procedure (make sure you toggled "Update Ids on Import" on)

Creating database backup in json format

Exporting data to json allows you to create a full database backup in human-readable form as json file. With json you can not choose which data you want to be exported/imported, full data including database structure will be included.

You can use this backup later to fully restore the database (including structure)

There are 2 different json file formats available:

1. Classic - legacy format(not recommended)
2. CompactRowBased - best readability
3. CompactFieldBased - best performance

Tables naming restriction

If you plan to use Excel/GoogleSheets, avoid giving your tables similar names with only difference in lower/upper letter(s) (like "test" "Test", "tESt" etc.). It will work fine with BGDatabase, however both Excel and GoogleSheets consider such names to be equal, and it will result in errors when you try to export the data.

Why you do not provide an option to update GoogleSheets data from player devices?

TLDR: Our export procedure is not meant to be executed simultaneously by multiple clients. If your app/game is running on a single device or if only one device can export data to GoogleSheets, you can use this plug-in

DETAILED EXPLANATION: To update data we need to read and analyse it first to determine which cells need to be updated and also optionally resize the sheet. Reading, analysing and updating data are not atomic operation. If somebody else updates the data during reading/analysis - writing data may result in data corruption/errors.

This problem can be solved with a proxy server, but it also means there is no need for direct access to GoogleSheets.

If you are still looking for a way to write data to GoogleSheets from players devices:

1. If all you need is to add data to spreadsheet, you can use Google Forms, linked to a spreadsheet and HTTP POST to add data to spreadsheet. Also, read our mini tutorial, how to use Google Forms to update GoogleSheets here
2. If you need more sophisticated control GoogleSheets dotnet API library v4 is probably the best starting point.

What's next?

Read product specific guides next

1. Excel/Open Office
2. Google Sheets