About Using Excel
Forum 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 data containing diacritics is preserved when imported into your project. This will preserve any special characters are included 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 Forum, you can export existing records, make global edits in Microsoft Excel™, and then import your revised data back into Forum. 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 where 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 Forum cannot be performed without exporting an Excel spreadsheet to generate a template first.
To generate an Excel template for an empty project:
- Above the item panel, select the Options menu and then Export All Records.
- Save the Excel sheet locally and then open it with Excel. 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 records.
- 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 item panel.
- Above the item panel, select the Options menu and select Export Selected Records..
- Save the Excel sheet locally and then open it with Excel.
- Select the second row that contains the record you exported and delete it, keeping the row above that contains the column headings.
- Save the template locally for easy access when importing new records to your projects.
Whether retrieving your data records to preserve locally, move elsewhere, or edit manually, Forum 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 (another limitation of the Microsoft Excel version).
To export all records from your project:
- After selecting your project, select the Options menu then Export All Records. If you have divided your records into sets or filters, you can also perform this when viewing the set or saved filter.
- Save the exported spreadsheet to a safe location locally and name the file something recognizable.