This page allows you to view and/or edit the definition for a single import/export specification. This specification defines the rules that will be used by Online Mooring during the import/export processes.
Import/Export Specification Fields
Specification Name - this is the name of the specification, which will be displayed on the import or export page. Please give a meaningful name that will allow you (and others) to understand how the data will be imported and/or exported.
Database Table(s) - this indicates what type of data will be loaded during the import/export process. If you choose the "Application" option, that will allow you to import/export all data relating to an application that has a single record per application, which includes information about the application itself, the applicant, the vessel, and the mooring.
For Export - if you want this import/export specification to be used for exporting data from Online Mooring to a CSV file, check this box.
For Import - If you want this import/export specification to be used for importing data from a CSV file into Online Mooring, check this box.
Header Row - If the first line of the CSV file contains column headings (or should contain column headings), check this box.
Continue on Error - If we encounter any errors during the import/export process, should we continue the process or stop processing? If you'd like us to continue processing, check this box.
Keep Data on Error - If we encounter any errors during the import/export process, should be leave any of the imported/exported data in its destination location, or should we remove the data that had been processed so far? To keep the data, check this box.
Allow Inserts - (imports only) If you want to allow the import process to add new records to the Online Mooring database, check this box.
Allow Updates - (imports only) If you want to allow the import process to update existing records in the Online Mooring database, check this box.
Import/Export Field Mapping
For each import/export specification, you'll need to specify what fields you want to import/export, and how to move the data between the database and the file. For each field, you can specify the following:
Database Field - This drop-down list lets you indicate a database field. On imports, data will be put into this field. On exports, data will be taken from this field and put into the file.
CSV Field - This lets you indicate the name of a field in the file. On imports, data will be taken from this field and put into the database. On exports, data will be put into this field. On exports where you have indicated that the file should have a header row, this text will be put into the header row. Normally, you can type any field name that you want, but if you have an existing CSV file that is in the format you want to import/export, you can upload that file and then the field names in that file will appear in a drop-down list.
Expression - If you want to put a value into a database or file that does not come directly from a field on the other side, you can enter an expression instead of a field name, and the value of this expression will be inserted into the specified destination. This can be useful if you want to import/export a hard-coded value, or a calculated value.
- For
imports, this can be any hard-coded value. For example, if you wanted to populate a column in the database to have a hard-coded value of X, enter
X in this field.
- For
exports, this can be any valid T-SQL expression. For example, if you wanted to populate a database column with a hard-coded value of X, enter
'X' in this field. If you wanted to calculate a value, such as a vessel length in inches, you could enter a formula such as
LengthInFeet * 12. For help with T-SQL expressions, click
here.
Position - (exports only) - This allows you to specify the order of the columns in your export file. The field in column 1 will appear first, followed by column 2, etc.
Format - This allows you to specify how to format data that can be formatted in more than one way. For example, dates can be formatted in US format (dd/mm/yyyy) or European format (mm/dd/yyyy). Lengths can be formatted in multiple ways (feet and inches, feet only, or meters).
Validation - (imports only) - This allows you to specify how to validated the incoming data for this field, if at all. The options are:
- Use value: we will import any values that are of the appropriate data type.
- Use value, add to list: we will import any values that are of the appropriate data type, and we will add all values to the list of valid values for this field if the values are not already listed.
- Use value, warn: we will import any values that are of the appropriate data type, and we will show a message during the import process if you try to import a value that is not in your existing list of valid values for this field.
- Do not use value, error: we will only import values if they contain a value that is in the current list of valid values for this field. If you try to import a value with an invalid value, we will generate an error, which may cause the import to stop if you have set up your specification to work that way.
Required - This indicates whether a value needs to be inserted into the destination location. If you indicate that the field is required, and no value is inserted into the destination, we will generate an error. This may cause the import/export to stop if you have set up your specification to work that way.
Examples
Example 1: Export fields from an application
Let's say you wanted to export five fields from an application: the Reference Number, the Length of the Vessel, the Registration/Documentation number of the Vessel, the berth number that the applicant has been assigned to, and the type of berth the applicant has been assigned to.
You would enter information about each of the fields into the "Add Field Mapping" area, clicking the + button after entering the information about each field, until it looks as follows.