This article will walk you through the essential steps of managing your data using Excel functionalities.
We’ll cover the following topics:
- Finding the Excel Export/Import Option
- Export Data to an Excel File
- Import Data from an Excel File
- Understanding the Excel Data Format
Finding the Excel Export/Import Option
Navigate to the Components screen, then locate and select Import Excel or Export Excel to initiate the process of importing data into, or exporting data from, your system.
Export to Excel
Gain control over your data as you prepare it for export. Customize what you export based on content, filtering preferences, and formatting needs.
Data Content
In this section you can choose what properties will be exported: Item amount, main panel value, visible property, color, background / fill color and/or the rest of the properties for all panel types.
Within this section of the export interface, you have the flexibility to select the specific properties of your data to export. The options available:
- Include item amount: Export the quantity of each item.
- Include panel main value: Export the primary value associated with each panel.
- Include panel visible property: Export properties that are set to be visible.
- Include panel color: Export the color attribute of each panel.
- Include panel background / fill color: Export background or fill color for panels.
- Include panel advanced properties: Choose to export additional, advanced properties for each panel type.
Data Filtering
This section offers you choices to refine the data that will be included in the export:
- Include blueprint values: When enabled, this will include all blueprint values. If disabled, only the properties of set items that have been overridden will be exported.
- Include blueprint default property values: This includes the default property values defined in the blueprint.
- Include non-editable blueprint panels: Choose to export properties for blueprint panels that are not editable.
- Include details not referenced by the blueprint: Export all data within the set, including data that is not currently used by the assigned blueprint.
- Skip unused column detail properties: Opt to exclude properties that are not overridden in at least one set item, reducing redundant data in the exported Excel file.
Data Formatting
Customize how your data will be formatted in the exported Excel file in this section:
- Include detail names in the first row: The first row will list the names of item details and properties, serving as headers for each column.
- Sort columns by detail name: Organize columns alphabetically by detail name, with the provision that the item amount column remains the first column if included.
- Force main panel to be sorted as the first column: The main panel, if defined in the blueprint, will be positioned as the first set of property columns, subsequent to the ‘item amount’ column if it’s included.
- Format NULL cells with light gray color: Cells corresponding to properties that are not overridden for an item will be shaded light gray to differentiate them from cells with overridden properties that contain empty string values.
Import from Excel
When you choose to import data from an Excel file, a dialog box will appear, guiding you through the process of configuring the data mapping between the Excel file, its sheets (Sets), and the corresponding set panels (details) and their properties:
Configuration
- First row contains the column names: Ensure the first row of your Excel sheet has the column names, which are crucial for the auto-mapping to function correctly.
- Delete all items before importing (replace mode): Selecting this option will remove all existing items in the set before importing new data from the Excel file.
- Update existing items in the set using the blueprint main panel as Item ID: The main panel value serves as a unique identifier to determine whether to update an existing item or create a new one during the import.
- Override all property values including null values from the Excel: If checked, all panel properties in the set will be reset to the values from the Excel file, even if they are null. If a cell should override a property with an empty string, it must contain the unicode character
U+200B(Zero width space) due to Excel’s handling of null data.
Auto-mapping
The Tabletop Creator tool will attempt to automatically map each sheet and data column within your Excel file to the corresponding set data, utilizing the column names provided in the first row of each sheet.
For the auto-mapping to be successful, the Excel sheets should be named in the following format:
- set_name@blueprint_name
Columns should adhere to one of these formats for proper mapping:
- [Item Amount]: This column will map to the item’s quantity.
- detail_name: Mapped to the main value property of a detail.
- detail_name:property_name: For example, a main_icon:color column will be mapped to a panel named main_icon for the property color.
Use the Reset Mappings button at the bottom left to clear and restart the auto-mapping process if needed.
Data Mapping
Adjust the mappings for each column in your Excel file to ensure the correct association with set details and properties. You can specify the target set (sheet) and the target panel (detail) along with its properties (value, color, etc.). If something does not align with the expected mapping criteria, a warning ⚠️ icon will appear on the right side of the row. Hovering your mouse over this icon will reveal a tooltip with information to guide you in making any necessary adjustments.
Common ⚠️ warning types include:
- [Sheet] Excel sheet is not mapped to any set: Your Excel sheet needs to have a name that matches one of your project’s sections.
- [Sheet] Set name does not exist in the project (will be created): We’ll create a new section in your project for any new set names from your Excel.
- [Sheet] Duplicated set mapping setting in another sheet: Each Excel sheet should be linked to a different section in your project.
- Column is not mapped to any detail name: Every column in Excel should match up with a specific field in your project.
- Detail name does not exist in the item (will be created): New fields will be added to your project for any new details from Excel.
- Column is not mapped to any detail property ID: Check that your Excel columns have titles that match the fields in your project.
- Property name is not used in the app (will be imported anyway): If your Excel has extra information, we’ll bring it in, and you can choose to keep it or not.
- Duplicated column mapping setting in another column: Make sure that each Excel column is set to link to a different field in your project.
Note: While it’s not necessary to resolve all warnings before importing data, it’s advisable to review and address them. Please ensure you read all warnings before proceeding with the import process.
Understanding Excel Data Format
Supported Format
Excel files utilize the XLSX format, also known as OOXML (Office Open XML SpreadsheetML). This format is widely supported for spreadsheet files.
Note: It’s important to note that older formats like XLS (Excel Binary file format) are not supported. Ensure that you use the XLSX format for compatibility.
Working with Excel Files
You can view, create, and edit your spreadsheet files using 3rd party apps like:
Sheet Structure
In an Excel file, each sheet contains a comprehensive dataset or set of data related to a specific context.
Row: Each row corresponds to a distinct item or entry within the dataset.
Column: The first row of the Excel sheet can optionally contain column names, but it’s not a requirement. Both the sheet name and the first row will be utilized by the Excel import screen to attempt auto-mapping of all the data.
Handling Empty Cells: An important consideration is that when a cell in Excel contains no data (an empty string), it is treated as NULL. In such cases, the associated property will not be overridden during the import process. If you need to override a property in your item with an empty string as its value, the cell must contain the Unicode character U+200B (Zero width space). This approach is necessary because Excel files do not support the concept of NULL data in cells.
