Setting

Definition

Job Name

Name of the job which will be used to refer to the job when performing this import in the future or setting up timed operations.

Type

Import or Import Merge

Note:  Import Merge jobs will not add records.  Merge jobs can only update existing MIE information.

Choose the schema into which the data will be imported.  Choose only schemas marked as IMPORT/EXPORT.  Do not choose schemas marked as EXPORT ONLY.

Delimiter

You can specify an alternate delimiter (other than a comma) for import jobs.  The default will be populated as a comma.  Another popular delimiter is the tilde (~) character.

This setting accepts general tags.

Upload (icon)

Click this icon and upload a .CSV file for import.  The filename must have the extension .CSV.

Download (icon)

Click this icon and download the currently defined .CSV file.  This allows you to use the currently defined file as a template for a new import.

Import from

Choose the file that was uploaded

Accept from

Enter a list of email addresses from which the job can accept the file to be imported.  When the file is submitted via email for import, it must be submitted from one of the addresses listed here.  Other criteria must also be met as defined in Emailing Files for Import.  If this setting is left blank, files will not be accepted for import via email.

Workflow (no errors)

If specified, this workflow will be fired when the job completes, if the job completed with no errors.

Workflow (any errors)

If specified, this workflow will be fired when the job completes, if the job encountered any errors.

Required access

Access level required to view this import job.

Allow mass updates ...

Applies to Import Merge only.  If there exist more than one record that matches the defined merge fields, and if this box is checked, then every matching record will be updated with the value(s) presented from the import file.  Changes will be logged for all records changed.

Remove

Applies to Import Merge only.  The character or characters placed in this setting will be temporarily removed from the match column containing existing MIE data and the import column.  This allows extraneous characters, such as dashes, underscores, and spaces, that may differ between the two data sets, to be removed prior to attempting to match.  The values are only removed to attempt a match - there is no effect on the data actually saved to the database.

Remove existing records not found in import file (Applies to top-most table)

Applies to Import only.  Checking this box will cause the MIE to check the destination table after import to make sure that it does not contain any records that were not sent in the import file.  This feature is used to maintain synchronization with some other master data source.  For example, if you are importing location information from a master system and each time the import file contains all locations, then you might check this box to make sure that any locations deleted from the master system since the last import will also get deleted from the MIE.  Note that this setting will only affect the top-most table in the import schema.  Data records in child tables will not be deleted.

Use this setting with caution.  Any associations that exist with the now deleted records will be broken (orphaned).  A better setting to use is the setting that allows you to mark non-existent records as inactive.

Mark existing records not found in import file as inactive

Applies to Import only.  Checking this box will cause the MIE to check the destination table after import to make sure that it does not contain any records that were not sent in the import file.  If it does, it will use the two settings below to mark any records that were not found in the import file as inactive.  This will cause the records to drop from end user view, however previous associations will be preserved to support reports and other research.

Mark inactive

Applies to Import only.  Choose the actual column in the destination table whose value will be set to mark the record as inactive.

Use caution - do not also import into this column.  If you do import into this column, the placing of the value specified to mark the record inactive will take precedence for those records that require marking.

Value for inactive

Applies to Import only.  The value placed here is the value that will be written to the specified column in order to mark the target record as inactive.

The MIE's convention is that True or Yes is designated by the value -1, while False or No is designated by the value 0.

Header

If the file to be imported contains only data, and no header row, then the header for the file must be specified here.  If a header is specified here, it will be used instead of the first row in the file (which will be interpreted as data).  If this setting is left blank, then the first row in the file will be used as the header row.  The header defined here must conform to the proper file specifications.  For example, if the file is piping character delimited (|), then the individual column headings must be separated using the piping character.

File substitutions

This setting allows defining a string to control character substitutions as the file is processed.  For example, if the file contains no intentional string delimiters (double quote character), and yet it does contain spurious double quote characters (which will be interpreted by the import engine as string delimiters), then those spurious characters can be stripped by using file substitutions.  File substitutions as defined as a series of search characters and substitute characters, as follows:

<Search1>=<Sub1>,<Search2>=<Sub2>,...,<SearchN>=<SubN>

Each search/sub pair are separated by an equals "=" sign, and each individual substitution definition is separated from the next by a comma.

This field accepts general tags.

Post processing SQL (ESI only)

This setting allows defining SQL statement(s) that will be issued after the import job has completed.  The SQL statement(s) will be executed only if one or more inserts/updates occur during the import.  This setting is only accessible to ESI.

This field accepts general tags.

Material Columns (15 level access or above)

This setting allows for defining one or more columns as “material”, meaning if data coming in through import will change the existing value in that column, that change constitutes a material change to the record.  Enter a comma-delimited list of actual MIE column names that are part of the defined import job (that are target columns against which you have mapped incoming data from the file to be imported). 

During import, incoming data will be checked against existing data in each of the columns in the list.  This is done row by row, as each incoming row is processed.  If the data in any of the columns will be changed by the incoming value, then the whole row is deemed to be a material change, and the row will be processed as normal.  If none of the columns in the list will be changed as a result of the incoming data, the row will be deemed immaterial and the incoming row will be ignored (will not be processed, even if there are changes to other, non-material, columns – the entire row will be skipped.)

If this setting is left blank, Import will consider all incoming rows as material and will process them as it otherwise normally would.

For import merge type jobs, materiality of incoming records will only be followed if the count of existing MIE rows that will be impacted by the incoming row is equal to 1.  For a merge type job that is permitted to make mass updates, any update that will result in more than one existing row being updated, the change will be considered material and will be processed as normal, regardless of whether or not any of the material columns has changed.

Always Write (15 level access or above)

This setting works in conjunction with the Material Columns setting.  It is only considered when one or more columns have been defined as Material Columns.  This setting allows you to define specific columns that will be updated anyway, in the event an incoming row has been deemed immaterial.  Furthermore, you can target the update to a column in an altogether different table – one that is not part of the actual import job definition.  The update will take the data from one of the incoming columns and write it to the target table/column, as defined in the setting.  You can target more than one column using this setting – just separate each column definition by a comma.

Column definition for Write-anyway columns are as follows:

<import column>[=<target table>.<target column>[.<target matching column>.<target table primary key column>.<import matching column>]]

Where:

<Import column> - the name of the MIE column into which one of the incoming import file fields has been crossed.  This is the MIE column name, not the name of the field from the import file.  However, the value that is coming from the import file that has been defined by the import job to be crossed into this field is the value that will be saved to whatever table/column you are targeting.

<Target table> - this is optional.  If you don’t include an equals sign and the information to the right of it, then the incoming value will be written to the table  and column as specified in the import job.  Use this to target some other table.

<Target column> - this goes with the <Target Table> setting.  It specifies the column within the <Target Table> where the incoming value is to be written.

<Target Matching Column> - this is a column within the target table that can be matched, uniquely, to this (or some other) incoming column.  For instance, you may wish to update the Census column in the ATdataAssets table, but you need to uniquely identify the exact record in the ATdataAssets table that needs to be updated.  If your import job also happens to be importing the asset number, then this setting would be Number (a column in the ATdataAssets table that is guaranteed to be unique within a given customer’s records.)

<Target table primary key column> - this is the column that is the primary key for the target table.  For ATdataAssets, for instance, this setting would be AssetID.

<Import matching column> - this is the MIE column, in the import job definition, where the value you need to match the <Target Matching Column> can be found.  In the example, if you have crossed the asset number into the ATdataIMACStage.AssetNumber column, then AssetNumber would be the value for this setting.  It tells the algorithm where to find the incoming value to be used to locate the target record in the target table.

Special Cases

Two table/column targets are handled as special cases.  They are:

ATdataAssetsXManagedSW.DateLastVerified and ATdataAssetsXManagedSW.DateScan

The target columns are kept in the authorizations table (ATdataAssetsXManagedSW) and are used to trigger auto-harvest activity.  To update these columns requires performing recognition of incoming software alias information in order to identify the proper authorization to update.  If you wish to target these columns with incoming date information, you need only specify the <Target Table> and <Target Column>, and leave it at that. 

<Import column>=ATdataAssetsXManagedSW.DateLastVerified

Or

<Import column>=ATdataAssetsXManagedSW.DateScan

If an authorization match is made, the target column will only be updated if the incoming date is an actual date and if it is later in time than the date value already in the column.