About Using Excel in Shared Shelf
Shared Shelf allows importing Excel spreadsheets to create new or edit existing records. If you include your media filenames in your Excel imports, then you can easily upload and sync your media files with the related data. These features can be particularly helpful if you are migrating out of a legacy system.
Use UTF-8 encoding to ensure your data that contains diacritics is preserved when you import it to your project. This will preserve any special characters you may have entered in your Excel spreadsheet. As a best practice, paste or type the new data into the spreadsheet generated from Shared Shelf, as opposed to cutting and pasting the column headings from the Shared Shelf-generated file into an existing spreadsheet. This can prevent stray data and extra spaces that might cause import errors. You can also prevent date fields from re-formatting in Excel by using a text field type in those columns, as exported by Shared Shelf.
Shared Shelf allows you to export existing records, make global edits in Microsoft Excel™, and then import your revised data back into Shared Shelf. Shared Shelf also allows you to import metadata first from an Excel spreadsheet and load corresponding media files later.
Generate an Excel Template
An Excel template is important to create when managing data externally to Shared Shelf. The Excel template includes columns for each of your Shared Shelf project fields as well as unique field ID numbers in brackets. These field ID numbers allow the system to recognize to which fields to migrate the data upon import. Exported records include a unique Shared Shelf ID number (SSID) that allows you to modify existing records. The process of importing data to Shared Shelf cannot be performed without exporting an Excel spreadsheet to generate a template first.
To generate an Excel template for an empty project:
- Above the top right of the asset panel, click the gear menu and select Excel > Export all records.
- In your downloads folder, or wherever your default location may be for downloads, find and open the Excel spreadsheet. The template contains specific elements:
- Column headings: identified by your project’s field labels and unique field ID numbers entered in brackets, which are not interchangeable between projects. Although you can edit the values in any of the cells below the columns, do not make any changes to the column headings to ensure the data can be imported back into Shared Shelf and mapped to the appropriate fields.
- Column A: defaults to SSID, or Shared Shelf ID. This is the unique ID number assigned by the system to each item record, which links the rows in Excel to records in Shared Shelf. In order to maintain the link to the corresponding records within Shared Shelf, when re-importing an Excel spreadsheet, do not change the values in the SSID columns, unless entering NEW to create a record.
- Column B: defaults to Filename, where you can enter the filename of the corresponding media. Note that the filename is case-sensitive, and the spelling and file extension must match the original media filename exactly. The filename column cannot support paths.
- Save the template to your hard drive for easy access when importing new records to Shared Shelf.
To generate an Excel template for a project with at least one record:
- Select at least one record from the asset panel.
- Above the asset panel, click the gear menu and select Excel > Export selected records.
- Find and open the exported spreadsheet.
- Select the second row that contains the record you exported and delete it, keeping the row above that contains the column headings.
- Save template to your hard drive for easy access when importing new records to Shared Shelf.
Whether retrieving your data records to preserve locally, move elsewhere, or edit manually, Shared Shelf allows a simple way to export all the records in your project. However the export all function has its limits. For instance, if your project contains more than 65,536 records, then the export will fail (a limitation of the Microsoft Excel version). Instead, consider exporting the data in batches using sets or filters and then combine the records into a single spreadsheet manually from Excel. Also, if any data fields contain more than 32,767 characters, exporting will likely cause an error (again, a limitation of the Microsoft Excel version).
To export all records from your project:
- Log into the Shared Shelf catalog, select the project, and navigate to the gear menu near the upper right corner.
- Click the gear menu and select Excel > Export all records.
- Save the exported spreadsheet to a safe location on your hard drive and name the file something recognizable.