Import/Export

Topics:

Importing

The Importer is available throughout BSI and provides a consistent way to import data from a .txt, .csv, or .xlsx file into a BSI table. If the file is imported from a Macintosh computer, the system will automatically adjust for the Macintosh version of Excel using the 1904 date system. Once the Importer wizard is started, the basic steps for importing a file are the same for any dialog. At any point after a file has been selected, clicking the Import button will complete the import using the Importer wizard default settings specified below for any subsequent importer dialog pages. The Importer can be exited at any time without importing a file by selecting the Cancel button.

Example: Dr. Jackson has collected several thousand new specimens from outside researchers and needs to document them in BSI. He already has the data stored in a .csv file. From his Data Entry Add batch, Dr. Jackson can import the data by uploading the file. His file uses the term “Alien Species”, while the corresponding field in BSI is named “Species”. He will need to match the correct column in the file to the BSI field.

Dr. Jackson will not select any fields to match on, since his specimens are all new entries to BSI. After importing, Dr. Jackson will be able to assign BSI IDs, reserve locations, and commit his new specimens to the database.

The Importer consists of several pages that are used to select a file and define the options for the import:

9vOjqMDRpCg0s174MlNl_temp.png

Importer Wizard Default Settings

The following defaults are applied when the user bypasses Importer wizard options by selecting the Import button (when Next is an option).

Import File properties

  • Comma or tab delimited file
    • Column Delimiter – Comma (,)
    • Row Delimiter – New Line
    • Grouping Marker – Double Quotes (“)
    • Skip the first __ rows—blank (0)
    • Use the first __ rows as header—one (1)
  • Excel file
  • Sheet Selection—the first sheet
  • Skip the first __ rows—blank (0)
  • Use the first __ rows as header—one (1)

Translator Set Selection—No Translation Required

Mapping to Output—map column headers where the BSI field label exactly matches text in the import file column header. Although the capitalization does not have to be the same for import column headers and BSI field labels, the text (including spaces) must be the exact same. The Importer will ignore those import file columns if their headers don’t exactly match BSI field labels, so they will not be imported into the database.

Matching

  • Matching Fields—None
  • Matching Options—Use database default

Append line if no match is found—selected

Import Templates

Import Templates can be created to automate the Properties, Translations, Mapping Fields, and Matching Criteria pages of the Importer wizard for file output formats you regularly receive. After manually entering your import settings once, on the Complete the Import page, you can select the option to save the import as a template. When importing a new, similar file from the Select a Data Source page, you can select the template you wish to use. Import templates will save the configured file type, sheet selection, delimiters, properties, translations, mapping fields, and matching criteria. If the saved sheet name isn’t found in your import file, you will be prompted to select the worksheet you want to import.

Specimen and Subject Importer

The Specimen and Subject Importer is available in both the Data Entry and Shipment batch editors from the File menu. The Specimen and Subject Importer functions similarly to the general Importer, in that it follows the same basic steps, but it also allows specimen and general subject data to be imported into BSI simultaneously.Importer

During the import, data can be mapped to both specimen fields and general subject fields. After all mapping is complete, the imported specimen fields will be added into the Data Entry or Shipment editor. After this, a subsequent subject editor is opened, and all remaining imported general subject fields are added to the subject editor. If subjects referenced in the import file already exist, then the subject editor will open with the existing subject data. Data that has been newly updated will be bolded in the subject editor. Newly created subjects referenced in the import file are imported into the editor and added as new subject records when the editor is saved.

If there are any conflicts between the imported subject data and existing subject data, the user will receive a warning. The warning displays the row in the import file that contains the conflict, the name of the conflicting field, the existing value for the field in BSI and the new value for the field in the import file. The user has the option to print the warning, continue with the import, or cancel the import.

Export to Text File

Several tables in BSI have an Export to text file option available from the File menu. Some dialogs also have an Export to Text File option. This option is useful for backing up and saving work so that it can be recovered, if necessary. Users can also Export with a Translation.

To export a current editor or dialog to a text file:

  1. Open the File menu in Selecting this option will open a Browse dialog that allows users to select a file name and destination on the local hard drive. The contents of the dialog will be stored in the selected location as a comma-delimited text file. The file can be imported into another application or back into BSI. Export to text file

Manifests

Manifests uploaded to a requisition or shipment via BSI Engage may be imported into BSI. To import the attached manifest:

  1. Open the requisition or shipment.
  2. Select File > Import Specimens.
    • Alternatively, in a shipment you may also select Import Specimens and Subjects.
  3. When the importer wizard opens, select Get Web Attachment.
  4. The manifest will be selected as the file to import.
  5. Proceed through the importer wizard to import the file.

In a shipment or requisition which has been submitted via BSI Engage, there may be a manifest attached. A property on the corresponding manager called “Manifest Name” which lists the name of the attached file, if there is one. Manifests may be exported to a local directory on your computer by selecting Tools > Export Manifest from the shipment or requisition editor.

In a Data Entry Add batch, using this tool can inadvertently cause BSI to resolve data conflicts by updating already entered sample level data. If this would happen, then a Sample Data Changed! dialog will open. The dialog shows the changes which will be made and provides an option to continue with the change, or to cancel the change.

After a manifest has been imported into an incoming shipment batch, changes to the imported data via manual data entry, scanning, or importing will open the Add Discrepancy dialog. Discrepancies in Location data will not prompt the Add Discrepancy dialog to open.

Select a Data Source

On the first screen of the Importer, select a data source:

  1. Click the Browse button to search for a data file.
  2. Once the file is selected, click Open.
  3. Select the file type (Delimited text file or Excel spreadsheet), if necessary.
  4. After selecting a file, there are threeoptions to continue with the import:
    • Manually process the import. Click Next to proceed without a template selected to the Select Data File Properties panel.
    • The Select Template button can be used to select a predefined import template to use. When selected, a dialog will open showing all of the available templates in your institution which match your selected file’s type. Selecting a template in this dialog will automate the middle steps of the importer wizard and take you straight to the Complete the Import page.
      • You will still be able to return to previous pages and edit any information necessary before importing the data.
      • If any warnings or errors would prevent the import from continuing, a dialog will open letting you know that you must choose a new template or manually complete your import. Any other warnings or errors will be displayed on the last page of the Importer wizard.
    • Select the Import button to bypass selecting data file properties, translations, field mapping, and matching criteria. The Importer wizard will use the defaults listed in Importer Wizard Default Settings. An Import dialog will open, displaying import progress and an option to save the import as a template.Data Source Selection

Select Data File Properties

Specify the file properties:

  1. Enter properties based on the type of file being imported:
    • When importing a delimited text file, identify the characters used as the Column Delimiter, Row Delimiter, and Grouping Marker in the text file.  
    • When importing data from an Excel file, select the Sheet that contains the specimen data.
  2. Enter the number of initial rows in the file to exclude from importing into the Skip the first _ row(s) field.  It is possible to skip up to the first 45 rows.
  3. If the text file contains column headers for the specimen data, check the Use first _ row(s) as header checkbox. Enter the number of rows (1-9), after any skipped rows, to use as the header. If multiple rows should be treated as the header, then BSI will delimit values with a single space between each row’s value. This whole term will be treated as the header. By default, marking the checkbox will use a single row as the header.  The column headers assigned will be displayed in the preview table.
  4. The specimen data are displayed in the preview table below in the format that will be imported into the batch.  Review the data and make changes to the file properties if necessary.
  5. Select Next to proceed to Select a Translation or select Import to bypass subsequent pages and complete the import with the defaults listed in the Importer Wizard Default Settings. Selecting Import will also cause an Import dialog will open, displaying import progress and an option to save the import as a template.Data File Properties

Select a Translation

Specify whether a Translation schema is required for the imported file data.

No Translation is selected by default. If no translation is to be used, click Next to continue to Map Imported Fields to Output.

If a Translation schema is to be used for the import, select Translation Required. This will enable the Select button next to the Translation Set field.

  • See Translation on Import in the Translations section of the help system for more information on using a Translation schema.Translation


Map Imported Fields to Output

Review the imported data and the Mapping to Output fields in the destination table.

  • If the imported text data does not contain headers or the column headers assigned do not match the intended output columns, select an output field value for each column of data. Any column that is not assigned a field value will not be imported
    • To manually map a column:
      1. Click on the column header for the column to be assigned. A list of the columns that can be mapped to the output dialog will be displayed.
      2. Select the column to be assigned. If the desired column is already assigned to a different column, it must first be unassigned.
    • To unassign a column:
      1. Click on the column header for the column to be unassigned. A list of the columns that can be mapped to the output dialog will be displayed.
      2. Select Leave Unmapped. The Leave Unmapped option is displayed at the top of the list of columns.
  • If desired, undo any changes made by selecting the Restore Defaults button.

  • When mapping is completed, select Next to proceed to Select Matching Criteria or select Import to bypass subsequent pages and complete the import with the defaults listed in the Importer Wizard Default Settings . Selecting Import will also cause an Import dialog will open, displaying import progress and an option to save the import as a template.Mapping Imported Data


Select Matching Criteria

When using the importer to modify a previously saved batch, users must select matching criteria for the imported specimen data. These criteria will specify the action taken when rows from the data source match rows in the BSI table.

  1. Review the list of Available Fields for matching. If a field from this list is selected, BSI will attempt to match imported data in this field with previously saved data.
    • To select all available fields for matching, click the double right arrow button (>>) .
    • To select specific fields for matching, use the mouse to highlight the field name and click the right arrow button (>). The highlighted field will be moved to the Selected Fields list.
    • Use the left arrow buttons to remove items from the Selected Fields list.
  2. Determine how matching rows from the imported data will be added to the table
    • Replace only empty field values on a matched line - This option will leave the original data unchanged, but will fill empty fields with data from matching lines in the imported data.
    • Replace all field values on a matched line - This option will replace entire rows in the table with matching lines from the imported data.
    • Append field values on a matched line - This option will append the field values in a matching line to the data in the table. It will not change the fields which are being used for matching.
    • Replace all field values on a matched line. Do not change import null values. - This option will not import blank spaces to the BSI field, but will still replace all values in the BSI field with existing imported field values.
  3. Check the Append line if no match is found box to add unmatched lines as new rows in the data table. Leave this box unchecked to import only matched lines.
  4. Select one of two options:
    • Next to review the import Summary and additional options before completing the import
    • Import to immediately complete the import and open the Importing dialog, which displays import progress and allows you to save the import as a template.

Note: When importing to a Data Entry Modify or Delete Batch, a unique ID must be specified in Selected Fields, either BSI ID or both the Sample ID and Sequence. The Importer will search the database to verify that the specimens from the import file exist. Any specimens in the import file that are not already in the batch will be added.Matching Search Criteria


Summary Review

The Summary Reviewpage of the Importer displays the file selection and import options specified for the import.

To complete the import:

  1. Review the summary of the selected import options.
    • If users wish to make changes before importing, use the Previous button to move back to the desired page and make the changes.
  2. Click Import to import the file. The Importing dialog will open and display the progress of the import.
    • If there are no errors with the import process, the Importing dialog will display the number of rows imported. Click Finish to close the Importer and return to the dialog.
    • If errors occur during the import, a descriptive error message will be displayed on the Importing dialog. The Importer will remain open. Click Finish to close the Importing dialog and return to the Importer.Importing dialog

You can save the import as a template by selecting “Save Template”. All parts of the import settings except the file name will be saved as a template to use in the future including: file type, sheet selection, file properties, translations, field mapping, and matching criteria.

After the file has been imported, the imported rows will be automatically highlighted in the dialog. From this page the user can press Print Summary to print the import summary displayed on this page.