Create, Edit, and Delete Data Using Excel

About Using Excel

Forum 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 data containing diacritics is preserved when imported into your project. This will preserve any special characters included in your Excel spreadsheet.

As a best practice, paste or type the new data into your 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 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.

Generate an Excel Template

An Excel template is important in managing data outside of Forum. 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:

1. Select the items you wish to export. From the Actions Bar, select Export Records. To generate a template for an empty project, select the project and click Download Excel Project Template
2. 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, 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.
3. Save the template locally for easy access when importing new records to your projects.

Create New Items with Excel

Creating new items using Excel can be an efficient way to migrate from a previous system, or manage workflows in an offline capacity. Since this process recommends importing the data first, it’s imperative to maintain the media filenames in the Filename field of the Excel spreadsheet. Once the data is added, uploading the media files follows. This allows the system to sync the media files with their corresponding data records.

To import new records using Excel:

1. Using the template you generated, in column A for SSID, input NEW in the row for each record you wish to create.
2. In column B for Filename, enter the filename of the corresponding media for the data. 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.
   
   
3. Catalog any other known data. For fields utilizing lists and linked authorities, see the article Excel and Specific Field Types. Note that Excel does have a character limit of 32,767 characters in any single cell.
4. Save the Excel spreadsheet as an .xls file. CSV files are not yet supported.
5. In Forum, go to the Add menu above the item panel and select Import Metadata.
6. Browse to the saved file on your local machine and import the spreadsheet.
7. The system will auto-generate and assign unique SSIDs for any records marked with NEW in the Excel spreadsheet. The new records will initially be marked with a placeholder image thumbnail, instead of the actual media file.
8. Go to the Add menu and select Upload Media and browse to your local machine to find the corresponding media files. Please see our recommendations for uploading media. Since the media filenames and extensions had been entered in the Filename field column on the Excel spreadsheet, the filenames are already populated in the data. The system will find the filenames of the uploaded media files and match them to the correct SSID based on the data. The initial placeholder thumbnail will then be replaced by the intended media file.

Recommended batch sizes are important to ensure successful imports of Excel data records.

• Importing data with Getty links, recommended batch size is 5,000 records or less.
• Importing data without Getty links, recommended batch size is 10,000 records or less.
• Bulk editing existing items using Excel, recommended batch size is 10,000 records or less.

Export and Edit Data in Excel

1. In the item panel, check the boxes next to the items you want to export. You can skip this step if you would like to export all records.
2. Go to the Actions Bar above the item panel and select Export Records.
3. A downloaded Excel spreadsheet will save to your local machine to open and edit.
4. After edits have been made, save the file.
5. In Forum, select the Add menu then select Import Metadata.
6. Browse to the saved Excel file on your local machine and then Import.

Important 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, if one did not exist, will link the data record to the file when that media file is uploaded.

Export and Delete Data in Excel

Data may be deleted from records in batch using Excel. Entering CLEAR in all capitals will trigger the system to remove the data from that field in one or more items upon import. The system does not recognize blank cells as edits to an imported record.

To clear field data:

1. In the item panel, check the boxes next to the items you want to export. You can skip this step if you would like to export all records.
2. From the Actions Bar, click Export Records.
3. Open the Excel spreadsheet, and input CLEAR into the appropriate cell(s) in your spreadsheet. The command is case sensitive. Save the file.
4. In Forum, go to the Add menu then select Import Metadata. Upon import, the system will clear previously entered data from these fields in each modified item.

The filenames of items can be cleared to make a bulk update of media files. View, Upload, and Replace Media Files includes instructions on how to batch clear and upload media.

Excel Error Reporting

After your Excel spreadsheet has been imported, 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.”