Create, Edit, and Delete Data Using Excel

About using Excel

You can create new items or edit existing items in Forum by importing an Excel (.xls) spreadsheet. You can also upload and sync media files with corresponding metadata by including media filenames in your Excel spreadsheet. This feature can be particularly helpful when migrating from a legacy system.

Tips:

• Use UTF-8 encoding to ensure data containing diacritics is preserved when imported into your project. This will preserve any special characters included in your Excel spreadsheet.
• For the best results, we recommend pasting or typing values into the Excel template generated from your project, as opposed to cutting and pasting the column headings from the Forum-generated file into an existing spreadsheet. This prevents stray characters and extra spaces that might cause errors upon import.
• You can prevent date fields from automatically formatting in Excel by using a Text field type in those columns.

Generate an Excel project template

To import data into Forum with an Excel spreadsheet, you'll first generate a project template.

The template includes columns for each of your project fields as well as unique field ID numbers in brackets in the column headers. These field ID numbers allow the system to determine where metadata should be mapped on import.

Exported records also include a unique system-supplied ID number (SSID) which you can use to modify existing records.

To generate an Excel template from an empty project:

1. Create a new project, or select an empty project.
2. Click the Download Excel project template button.

To generate an Excel template from an existing project:

1. Select your project, then select the items you wish to export.
2. From the Actions Bar, select Export Records.

Your template contains the following columns:

1. • SSID (Column A) - This is the unique ID number assigned by the system to each item, which links the rows in Excel to items in your project. In order to maintain the link to the corresponding records in your project, don't change the values in the SSID column when re-importing an Excel spreadsheet, unless you're using the value NEW to create new records.
   • Filename (Column B) - The filename of the corresponding media. Filenames are case-sensitive, and the spelling and file extension must match the original media filename exactly. The filename column does not support file paths.
   • Field columns - Identified by your project’s field labels and unique field ID numbers in brackets. Field ID numbers are are not interchangeable between projects. To ensure metadata can be imported back into your project and mapped to the correct fields, don't make any changes to the column headings.

Create new items with Excel

When creating new items with Excel, you'll first import the item metadata, including media filenames, using your project template spreadsheet. Then you'll upload the corresponding media files, and Forum will link the media files to their corresponding records by the filename.

To create new items using Excel:

1. In your template spreadsheet, enter NEW (case-sensitive) in the SSID column (Column A) for each record (row) you wish to create.
2. Enter the filename of the corresponding medium in the Filename column (Column B).
   • Filenames are case-sensitive, and the spelling and file extension must match the original media filename exactly.
   • The filename column doesn't support file paths.
     
3. Catalog any other known metadata. For fields utilizing lists and linked authorities, see Excel and Specific Field Types. Note that Excel has a character limit of 32,767 characters in a single cell.
4. Save the Excel spreadsheet as an .xls file. (CSV files are not yet supported.)
5. In your project, open the the Add menu and select Import Metadata.
6. Browse to and select the spreadsheet on your local machine.
7. The system will auto-generate and assign unique SSIDs for any records marked with NEW in the spreadsheet. New records will be initially marked with a placeholder thumbnail image.
8. Open the Add menu and select Upload Media.
9. Browse to and select the corresponding media files on your local machine. Please see our recommendations for uploading media. Forum will match the uploaded media files to their corresponding records by the filename in the metadata and the initial placeholder thumbnail will be replaced by the media file thumbnail image.

Export and edit items with Excel

1. In your project, select the items you want to export.
   • To export all items in a project, select all items on the page using the checkbox at the top of the item panel, then click "Select all ___ items in the project" in the banner message that appears.
     
2. In the Actions Bar select Export Records.
3. Open the downloaded Excel spreadsheet, edit the metadata, and save your changes.
4. In your project, open the Add menu and select Import Metadata.
5. Browse to and select the Excel spreadsheet on your local machine and then select Import.

Notes:

• Excel has a limit of 65,536 rows. Exceeding this number of exported records is likely to cause an error. We suggest using Sets or Saved Filters to export your data in batches less than 65,536 records.
• Excel has a limit of 32,767 characters in any single cell. Exporting or importing more than 32,767 characters in any cell is likely to cause an error with Excel.
• The SSID column heading must be present in an import and remain unchanged. When importing an edited Excel spreadsheet, do not change the values in the SSID column, unless you are creating new records, in which case you may replace the SSID with NEW.
• The Filename column heading must be present in an import and, like all other column headings, must remain unchanged. Entering a filename will link the record to the file when that media file is uploaded.

Export and delete items with Excel

Data can be batch deleted from records using Excel. Entering CLEAR in one or more cells will trigger Forum to remove the data from that field for that item upon import.

Item filenames can be cleared to make a bulk update to media files. See View, Upload, and Replace Media Files for instructions on how to batch clear and upload media.

To clear field data with Excel:

1. In your project, select the items you want to export.
   • To export all items in a project, select all items on the page using the checkbox at the top of the item panel, then click "Select all ___ items in the project" in the banner message that appears.
     
2. In the Actions Bar, select Export Records.
3. Open the Excel spreadsheet, enter CLEAR (case-sensitive) in the appropriate cell(s), and save your changes.
4. In your project, open the Add menu and select Import Metadata. Forum will clear previously-entered data from these fields in each modified item.

Recommended batch sizes

When importing or editing items with Excel, we recommend the following batch sizes for best import results.

Excel error reporting

After performing an Excel spreadsheet import, a window will appear displaying a summary of the number of items modified and the number of newly-created items. If any errors prevented the import, an error message will be displayed. If multiple errors occurred for a single record, each error will be represented in its own line item at the bottom of the window, along with a description.

Reasons an import may fail include incorrect field IDs in the header, terms not matching to a list, or linked authorities not formatted correctly for import. Contact forumsupport@jstor.org if you have questions about Excel error reports.

To download and save this report, select Download Errors or to cancel the import, select Cancel Import.

• Downloading the report will reopen the spreadsheet in Excel and fields with errors will be highlighted in red.
• For fields using lists, nonconforming values that repeat in the spreadsheet are flagged once, but all instances must be corrected before attempting to re-import.
• If a cataloger edits a fields in the Excel spreadsheet which are read-only for them, the following error message will be displayed: “Read only fields detected during import. Any changes to read only fields have been discarded.”