Download PDF

On this page…
Create New Records with Excel
Export and Edit Data in Excel
Export and Delete Data in Excel
Excel Error Reporting

Create New Records with Excel

Creating new records 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 and match 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, type 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 file as an Excel spreadsheet as an .xls file. CSV files are not yet supported.
  5. In Forum, select the Options menu above the item panel and then Import Records.
  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. Above the item panel, click the Upload button and browse to yourlocal 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.

Important notes:

  • 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 records using Excel, recommended batch size is 10,000 records or less.

Export and Edit Data in Excel

To export and edit all items in Excel:

  1. In the item panel, select the Options menu in the top right and select Export All Records.
  2. A downloaded Excel spreadsheet will save to your local machine to open and edit.
  3. After edits have been made, save the file.

To export and edit selected items in Excel:

  1. In the item panel, check the boxes next to the items you want to export.
  2. Select the Options menu and then Export Selected 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.

To import edited items using Excel:

  1. In the item panel, select the Options menu and then select Import Records.
  2. 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.
  • Maintain the column headings so the data can be imported back into your project and mapped to the appropriate fields.
  • 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 bulk using Excel. Entering CLEAR in all capitals will trigger the system to the clear 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. Select the items from which you wish to clear data, then use the Options menu and select Export Selected Records.
  2. Open the Excel spreadsheet, and enter the text CLEAR into the appropriate cell(s) in your spreadsheet. The command is case sensitive. Save the file.
  3. Back in Forum, use the Options menu and select Import Records. Upon import, the system will clear previously entered data from these fields in each modified item.

*Important note: The filenames of records can be cleared to make a bulk update of media files. Follow the steps, and then upon import, the Filename field will be cleared while the placeholder thumbnail will take the place of the original media file. Repeat the steps again, but replace CLEAR in the Excel with the new filename (sensitive to spelling, case, and file extension), and then import. Once the filenames populate in the data, upload the new media files to sync with the data records. More instructions can be found at http://support.forum.jstor.org/article/working-with-media-files#BatchReplaceMediaFiles.


Excel Error Reporting

After your Excel spreadsheet has been imported, a window will appear displaying a list of the number of items modified and the number of newly created items. If any errors prevented the import, an error messaging will be displayed here. 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, click Download Errors. OR to cancel the import, click 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 their layout, the following error message will be displayed: “Read only fields detected during import. Any changes to read only fields have been discarded.”