Creating, editing, and deleting data using Excel [Tiers 2-3]

About using Excel

You can create new items or edit existing items in a project 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 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.

Generating an Excel project template

To import data 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 existing project:

1. Select your project, then select one or more items you wish to export.
2. Open the three-dot overflow menu and select Download Metadata.

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. Filenames must be unique.
   • 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.

Creating 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 the system 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.
   • Filenames must be unique and can only be associated with a single record per project.
   • 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 [Tiers 2-3]. 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 drop-down menu and select Import Metadata.
6. Browse to and select your spreadsheet and select Import.
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. Next, open the Add drop-down menu and select Upload Media.
9. Browse to and select the corresponding media files.
   • Please see our recommendations for uploading media.
   • The system 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.

Exporting and editing items with Excel

1. In your project, select all items you want to export.
   • At this time, exporting project records is limited to the rows visible in the project table with a maximum of 250 items per page. If your project contains more than 250 records, please export records one page at a time.
2. Open the three-dot overflow menu and select Download Metadata.
3. Open the downloaded Excel spreadsheet, edit the metadata, and save your changes.
4. In your project, open the Add drop-down menu and select Import Metadata.
5. Browse to and select your Excel spreadsheet and select Import.

Notes:

• Excel has a limit of 1,048,576 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 1,048,576 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.

Exporting and deleting items with Excel

Data can be batch deleted from records using Excel. Entering CLEAR (case sensitive) in one or more cells will trigger the system 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 Viewing, uploading, and replacing media files [Tiers 2-3] for instructions on how to batch clear and upload media.

To clear field data with Excel:

1. In your project, select all items you want to export.
   • At this time, exporting project records is limited to the rows visible in the project table with a maximum of 250 items per page. If your project contains more than 250 records, please export records one page at a time.
2. Open the three-dot overflow menu and select Download Metadata.
3. Open the download Excel spreadsheet, enter CLEAR (case-sensitive) in the appropriate cell(s), and save your changes.
4. In your project, open the Add drop-down menu and select Import Metadata.

The system 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.

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. Please contact JSTOR Digital Stewardship Services Support 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.”