The OpenAsset Spreadsheet importer enables you to load in Projects, Employees or Text Assets in bulk using Excel spreadsheets. This is typically useful for when you are starting to load content into OpenAsset but it can be used at any time. The importer is currently limited to adding new assets and does not facilitate updating existing assets, although this is a capability that we are likely to introduce in the future.
Feature Access
In order to access the spreadsheet importer you need to first enable it by turning on:
System Settings > System Preferences > Enable Spreadsheet Importing
You then need to be in the Administrator group to be able to use the feature.
High Level Workflow
At a high level, the workflow for loading data using spreadsheets is:
Download a spreadsheet template for the asset type that you want to load
Fill out the spreadsheet template
Upload it to populate OpenAsset with the data
See reporting on the data load
Step by Step Instructions
There are 2 ways to access the spreadsheet importer:
System Settings > Imports & Exports > Importer
When adding a new Project, Employee or Text Asset you have the option of adding in bulk via a spreadsheet
When you enter the Importer page, click the “New Import” button.
Choose to download a template for the kind of asset that you want to import.
The file will download as an .xlsx file, editable natively in Microsoft Excel, although it’s also possible to edit these in other spreadsheet applications such as Google Sheets. Just be aware that you will need to save it as an .xlsx again in order to import it.
Filling out data in the spreadsheet templates
When you open a downloaded spreadsheet template you will see a header in the first row that contains columns for all of the fields that you are able to import. Required fields are colored purple. These must be filled out in order for the asset to successfully import. The green columns are optional to fill out.
Keep the header row exactly as it is. There is no need to remove or rename columns. If you do now want to fill out an optional column, just leave it blank.
If you already have a spreadsheet of data, say exported from another system, copy and paste the data into the template column by column so that you retain the template’s format.
Field Types
Most fields can be filled out as freeform text with the exception of:
Date Fields - you can use a range of date formats to enter dates.
Yes/No Fields - These should be entered as "Yes" and "No" or "1" and "0".
Project Templates
Project Templates have a required column called “code”. This must be unique against other projects in your system else the project will not import.
Project Templates also contain columns for Project Keyword Categories. You are able to add more than one keyword to a column by separating them with commas.
Employee Templates
Employee Templates have a required column called “code”. This must be unique against other projects in your system else the project will not import.
Text Asset Templates
Text Asset Templates have multiple tabs. Each tab represents a Text Asset Category. Text Asset Categories can have different configurations of fields, so you will see the appropriate field columns within each tab.
You are able to populate both the Text Asset content fields and reference fields via a spreadsheet import.
Importing your data
Once you have prepared your spreadsheet to import, click the “New Import” button again and choose to “Upload a completed template”.
You are then able to either drag and drop to file or browse your system to upload it.
When you start an upload you will see a progress bar as the upload is taking place.
When the upload is complete you will get a message that explains how the upload went. Either:
Successful
Partially Successful (Some assets failed to import)
Invalid (The file uploaded was not valid)
In the case of Partial uploads, you will have the option of downloading a spreadsheet with the problematic rows. This means you are able to fix the issues and re-upload again.
Reasons for Invalid files could be if the file is not an .xlsx, if the columns are not correct, or if it does not contain the header row from a template. Note that this header row contains hidden information that is important for the import. These issues can be avoided by always using the template files and not adjusting their format.
Your Import History
Every import attempt is listed on the importer page showing when it was uploaded and by who.
