About Using Excel
The Cataloging Tools allow 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 your project, as opposed to cutting and pasting the column headings from the Forum-generated file into an existing spreadsheet. This can prevent stray data and extra spaces that might cause errors upon import. You can also prevent date fields from re-formatting in Excel by using a text field type in those columns.
From the Cataloging Tools, you can export existing records, make global edits in Microsoft Excel™, and then import your revised data back into the Cataloging Tools. Using this feature, you can also import metadata first to create new records from an Excel spreadsheet and load corresponding media files later.
Generate an Excel Template
An Excel template is important to create when managing data outside the system. The Excel template includes columns for each of your project fields as well as unique field ID numbers in brackets. These field ID numbers allow the system to determine into which fields data should be migrated upon import. Exported records include a unique system-supplied ID number (SSID) that allows you to modify existing records. The process of importing data into the Cataloging Tools 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 your project and mapped to the appropriate fields.
- Column A: defaults to SSID. This is the unique ID number assigned by the system to each item record, which links the rows in Excel to records in your project. In order to maintain the link to the corresponding records in your project, do not change the values in the SSID columns when re-importing an Excel spreadsheet, 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 your projects.
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 your projects.
Whether retrieving your data records to preserve locally, move elsewhere, or edit manually, the Cataloging Tools allow 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 Cataloging Tools, 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.