Add an import descriptor record

Applies to User Roles: System Administrator

Service Manager enables you to add import descriptor records for two types of text files: character-delimited, and fixed-width.

Add an import descriptor record for a character-delimited text file.

To add an import descriptor record for a character-delimited text file, follow these steps:

1. Click Tailoring > Database Manager.
2. Click More or the More Actions icon, and then select Text Import Wizard.
3. Fill in the following fields.
   
   

   Field	Description
   File to Import	Type or browse to the path and name of the external text file to import. Note Only Windows Dos text file format is supported.
   Member	This field is obsolete.
   Destination Table	Type the database dictionary name of the table receiving the imported records.
   File Type	Select the source operating system of external file. Note The Winnt file type is recommended.

4. Click Search.
   Service Manager searches for the external file and displays one of the following messages:
   • If Service Manager finds the external file but there is not existing import descriptor record, Service Manager displays the form to create a new import descriptor.
   • If Service Manager finds the external file and also finds one or more import descriptor records, Service Manager displays a list of import descriptor records.
   • If Service Manager cannot find the external file, it displays an error message.
5. If Service Manager displays a list of import descriptor records, do one of the following:
   • Click an existing import descriptor record.
   • Click New.
6. From the Creating a New Import Descriptor form, select Character-delimited for Input File Type.
7. Click Proceed.
8. Fill in the following import data definitions.
   
   

   Field	Description
   Import Name	Type the name used to save the import descriptor record.
   Import Table	This field lists the destination table you selected earlier in the wizard. This is a read-only field.
   Import Mode	Select one of the following options: Add: Select this option to only add new records. Add/Replace: Select this option to both add new records and replace existing records.You may also select this option to update fields that you define in the Advanced tab. Replace only: Select this option to only replace existing records. Note If you use field mapping for importing, Service Manager always use the Add/Replace import mode regardless of the mode you specified.
   Format Control	Type the name of the optional Format Control record used to format the incoming records. Note For the Format Control record, only the following sections are supported: Calculations, JavaScript, and Validations.
   Delimiter	Service Manager only displays this field on character-delimited imports. Select one of the following options: Comma: Select this option if the import file uses a comma (,) between fields. Tab: Select this option the import file uses a tab character between fields. Semicolon: Select this option if the import file uses a semicolon (;) between fields. Other: Select this option to specify the character the import file uses between fields. Note Delimiter is set to Comma by default. However, when you add a descriptor for the import file, if your source file uses a comma as the delimiter, you must select other delimiters first and then reselect Comma. Alternatively, you can first click Add to add a descriptor, and then click Back to return to the descriptor. You need to do so to trigger the retrieval of the source columns from the source file using the specified delimiter. Otherwise, you will see a blank Source Column list.
   Text Qualifier	Service Manager only displays this field on character-delimited imports. Specify the text qualifier the import file uses to include special characters such as text delimiters in the record data.
   Array Separator	You can specify this field to split array data in your source file. Service Manager splits the array data based on the Array Type field. Select one of the following separators for your array data: Semicolon: Select this separator if the source file uses a semicolon to split the array data. Line Break: Select this separator if the source file uses a line break to split the array data. For example, when data is stored as ‘va;vb’, and Semicolon is selected in the Array Separator drop-down list, Service Manager parses the value to [‘va’,’vb’].
   Field Name	Specify the database dictionary field name receiving the imported record data. Type one field name for each source field in the import file.
   Source Column	This is a list of column headers in the source file. Service Manager automatically retrieves the list according to the delimiter you specified. If you specify a wrong delimiter, the source column retrieval fails. You should map each source column to a field in the target table. If the mapping table contains a field that is not mapped to a source column, all mappings are ignored. Instead, all specified fields are automatically mapped to the source columns in their display order in the source file. One source column can be mapped to multiple target fields. For example, a Service Catalog item can have the same value for Name and Display Name. Name Display Name PC Hardware PC Hardware If you are importing Service Catalog items into Service Manager, you can map the Name column in your source file to the name and displayName fields in the svcDisplay table in Service Manager, as shown in the following table. Field Name Source Column name Name displayName Name You cannot specify duplicate field names in the mapping table, that is, each field name can only occur once in the mapping table. Service Manager returns an error if it detects a duplicate field name. Only the specified source columns are imported.
   Field Type	This is a read-only field and cannot be edited. If Service Manager validates the data from the import file, it displays a number describing the data type.
   Date Format	Type the date and time format used to import date and time fields. You can use the following tokens to represent units of time: dd: two digit day mm: two digit month yy: two digit year yyyy: four digit year hh: hours ii: minutes ss : seconds When you specify a mapping between the source columns and target fields, yyyy is not supported. When you load date type records, the date format is converted to the same as the login user’s. For example, the date format in the external file is "mm/dd/yy" with the value of "07/12/14", and defined in the mapping table. However, the login user uses a date format of "yy/mm/dd". In this case, the date format is converted to "14/07/12" and saved in Service Manager.
   Array Type	This is a read-only field and cannot be edited. If Service Manager validates any array data from the import file, it displays a number describing the array type.
   Occurrences	Type the number of array elements to import for this field.
   Query	Type a query used to select an existing record from the destination Service Manager database for updating. If this query returns a single record, Service Manager updates this record with the data from the import file. If the query returns more than one record, then Service Manager uses the Import Mode setting to determine what action to take. Note The import utility stores the values of imported fields in the $L.db variable. You can use this variable to construct queries. For example: not null(name in $L.bd)
   Skip	Type a query that identifies records to skip when adding or updating from the import file. If this query returns one or more records, Service Manager ignores adds or updates from records matching the query. To use this field you must also provide an entry in the Query field. Note  The import utility stores the values of imported fields in the $L.db variable. You can use this variable to construct a skip condition query. For example: null(name in $L.bd)
   Expressions	Type any RAD expressions used to identify records for importing. Note If you have specified a correct mapping between the target fields and source columns, RAD expressions are disabled.
   Javascript	Specify Javascript code to do advanced import. The JavaScript expressions are executed against each record in the source file. For example, when importing your Outlook contacts data into the contacts table in Service Manager, you can combine the First Name and Last Name, and then import the combined value into the contact.name field in the contacts table. First Name Last Name John Smith After the combination, a contact record John, Smith is inserted into the contacts table in Service Manager. To achieve this, you can define the following statement in the JavaScript field: target[‘contact.name’] = source[‘First Name’] + ‘,’ + source[‘Last Name’]; where the target object represents the Service Manager target field, and the source object represents one record in the source file.
   Update Field Names	Type the list of field names from the destination table used to uniquely identify a record. If Service Manager finds existing records matching these fields, it updates the record with the data from the import file.
   Case Conversion	Select one of the following options to convert record data to a specific case: None: Select this option if you do not want to convert the case of imported data. Upper case: Select this option to convert imported data into all upper case. Lower case: Select this option to convert imported data into all lower case.
   Skip Lines	Specify the optional number of lines to skip at the beginning of the file. Note When you specify a mapping between the source columns and target fields, you must set Skip Lines to 1 which means the column headers is specified as the first line.

9. Click Add.

Add an import descriptor record for a fixed-width text file.

To add a fixed-width import descriptor record, follow these steps:

1. Click Tailoring > Database Manager.
2. Click More or the More Actions icon, the form and select Text Import Wizard.
3. Fill in the following fields.
   
   

   Field	Description
   File to Import	Type or browse to the path and name of the external text file to import.
   Member	This field is obsolete.
   Destination Table	Type the database dictionary name of the table receiving the imported records.
   File Type	Select the source operating system of external file.

4. Click Search.
   Service Manager searches for the external file and displays one of the following messages:
   • If Service Manager finds the external file but there is not existing import descriptor record, Service Manager displays the form to create a new import descriptor.
   • If Service Manager finds the external file and also finds one or more import descriptor records, Service Manager displays a list of import descriptor records.
   • If Service Manager cannot find the external file, it displays an error message.
5. If Service Manager displays a list of import descriptor records, do one of the following:
   • Click an existing import descriptor record.
   • Click New.

6. From the Creating a New Import Descriptor form, select Fixed-width for Input File Type.

   Field

   Description

   Fixed-width

   Select this option if each field in the file occupies a fixed-width position. For example, the following are two records using fixed width positions. The number field starts at position 1 and ends at position 6. The open.time field starts at position 8 and ends at position 15. Position 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Record 1 I M 1 0 4 2   1 2 / 2 9 / 0 0 Record 2 I M 1 0 5 8   1 2 / 2 9 / 0 0

7. Click Proceed.
8. Fill in the following import data definitions.
   
   

   Field	Description
   Import Name	Type the name of the new import descriptor record.
   Import Table	This field lists the destination table you selected earlier in the wizard. This is a read-only field.
   Import Mode	Select one of the following options: Add: Select this option to only add new records. Add/Replace: Select this option to both add new records and replace existing records. You may also select this option to update fields that you define in the Advanced tab. Replace only: Select this option to only replace existing records.
   Format Control	Type the name of the optional Format Control record used to format the incoming records.
   Field Names	Specify the database dictionary field names receiving the imported record data. Type one field name for each source field in the import file.
   Field type	This is a read-only field and cannot be edited. If Service Manager validates the data from the import file, it displays a number describing the data type.
   Start Position	Service Manager only displays this field on fixed-width imports. Type the column number where data for this field starts.
   Field Length	Service Manager only displays this field on fixed-width imports. Type the number columns that the data from this field uses.
   Array Type	This is a read-only field and cannot be edited. If Service Manager validates any array data from the import file, it displays a number describing the array type.
   Occurrences	Type the number of array elements to import for this field.
   Date Format	Type the date and time format used to import date and time fields. You can use the following tokens to represent units of time: dd: two digit day mm: two digit month yy: two digit year yyyy: four digit year hh: hours ii: minutes ss: seconds You can use any character to separate these tokens. For example, mm/dd/yyyy hh:ii:ss imports dates and times with slashes between the month, day, and four-digit year and colons between the hours, minutes, and seconds.
   Query	Type a query to select one existing record from the destination Service Manager database for updating. If this query returns a single record, Service Manager updates this record with the data from the import file. If the query returns more than one record, then Service Manager uses the Import Mode setting to determine what action to take.
   Skip	Type a query to identify records to skip when adding or updating from the import file. If this query returns one or more records, Service Manager ignores adds or updates from records matching the query.
   Expressions	Type any RAD expressions used to identify records for importing.
   Update Field Names	Type the list of field names from the destination table to uniquely identify a record. If Service Manager finds existing records matching these fields, it updates the record with the data from the import file.
   Case Conversion	Select one of the following options to convert record data to a specific case: None: Select this option if you do not want to convert the case of imported data. Upper case: Select this option to convert imported data into all upper case. Lower case: Select this option to convert imported data into all lower case.
   Skip Lines	Specify the optional number of lines to skip at the beginning of the file.

9. Click Add.

Delete or update an import descriptor record.

To delete or update an import descriptor record, follow these steps:

1. Click Tailoring > Database Manager.
2. Type =import in the Form field.
3. Select Administration Mode. You must select this option for Service Manager to display the Delete button.
4. Click Search.
5. Click the form where both the Format Name and File Name equal import.

6. Click Search again to display a list of import descriptor records.

   Note You may need to click Document > Restore to see the list of import descriptor records.

7. Select the import descriptor record you want to update or delete.
8. To update the descriptor record, update the fields and then click Save.
9. To delete the descriptor record, click Delete, and then select Yes.

Related topics

Data maps
Exporting records
Importing records
Import descriptor records
Unload files
Purging and archiving records
Unload script utility
Master data records

Load error messages and notifications
Import a character-delimited text file
Import a fixed-width text file
Load an unload file
Schedule the importing of records using an import descriptor record
Schedule the loading of an unload file
View the contents of an unload file