General Functions > Import / Export > Import

As you cross import fields to MIE destination fields, you can specify that the value being imported be transformed as it is saved to the MIE. This is specified in using the Transform column. Some transformations also expect values to be placed in the Constant column. The following table documents the transformations as well as any parameters required to be in the Constant column:

Transformation	Parameters (entered in the Constant Column)	Purpose
*MERGE 1ST->	character substitutions	Only honored for Import Merge jobs. Specifies the first field crossing to be used to attempt to match the incoming (CSV) record to an existing MIE record.
*MERGE 2ND->	character substitutions	Only honored for Import Merge jobs. Specifies the second field crossing to be used to attempt to match the incoming (CSV) record to an existing MIE record. This will only be used if the first field crossing fails to match.
*MERGE 3RD->	character substitutions	Only honored for Import Merge jobs. Specifies the third field crossing to be used to attempt to match the incoming (CSV) record to an existing MIE record. This will only be used if the first and second field crossings fail to match.
-->		No transformation, data is placed in MIE field as is (with possible truncation if data is longer than field length)
BlankOnZero		If the import value is numeric and is equal to zero, a single blank will be stored. Otherwise, the value itself is stored.
Boolean		If the import value is "NO" or 0, 0 is stored. Otherwise, -1 is stored.
CURRENCY		If the value is less than 1000, then the number stored will contain two digits past the decimal point. Otherwise, the number stored will contain no digits past the decimal point.
CURRENCY-2		The value stored will contain two digits to the right of the decimal point.
GenMOD137	<value>	Converts the data to a MOD137 compliant number. <value> is sent as the fixed length of the resultant MOD137 compliant number. For example, putting 8 in the Constant Column will cause an 8 digit MOD137 compliant number to be returned. The data sent must be numeric. If it is not numeric, it will not be transformed.
HEX		The value is stored as a hexadecimal number. If the value is non-numeric, 0 will be stored.
HEX6		The value is stored as a fixed length, 6 digit hexadecimal number. If the value is non-numeric, "000000" will be stored.
IF-BOOLEAN	<value true>\|<value false>	If the value is one of "N", "NO", "0", or "" (blank), <value false> is stored. Otherwise, <value true> is stored. The test is not case sensitive.
IF-EQUALS	<test>\|<value true>\|<value false>	Tests the value to see if it is equal to <test>. If so, <value true> is stored. Otherwise, <value false> is stored. The test is not case sensitive and also is performed after any leading or trailing spaces have been stripped.
IN	<value>	If the <value> entered is found anywhere in the import value, "Y" is stored in the MIE field, otherwise, "N" is stored.
LEFT-COMMA		The information to the left of the first comma (if any) of the import value is stored. If no comma is found, the entire value is stored.
LEFT-SP		The information to the left of the first space (if any) of the import value is stored. If no space is found, the entire value is stored.
MID	<start> \| <length>	The portion of the import value starting at character position <start> and consisting of <length> characters is stored. If <length> is greater than the remaining number of characters in the import value, the rest of the import value, starting at character <start> is stored.
mmddyyyy		If the import value is blank, 8 spaces will be stored. If the import value is non-blank, it is assumed to be a date value and will be stored as a number that represents the Month, Day, and Year, run together without any separation between the values.
mmyy		If the import value is blank, 4 spaces will be stored. If the import value is non-blank, it is assumed to be a date value and will be stored as a number that represents the Month and Year, run together without any separation between the values.
NN		If the import value is blank, two blank spaces are stored. Otherwise, a 2-digit numeric value, with leading zero, is stored.
REPLACE	<search>\|<replace>	Replaces all occurrences of <search> with <replace> in the import value.
RIGHT-COMMA		The information to the right of the first comma (if any) of the import value is stored. If no comma is found, the entire value is stored.
RIGHT-SP		The information to the right of the first space (if any) of the import value is stored. If no space is found, the entire value is stored.
SPLIT	<N>\|<delimiter>	The data is split into sub-fields based on the <delimiter>. The <N>th sub-field is returned. Sub-field numbering (<N>) starts at 0. If <N> is greater than the number of sub-fields, then an empty string will be returned.
SPLIT-COMMA	<N>	The data will be split into sub-sections based on a comma delimiter, and the Nth sub-section will be returned. N begins at 0. If N is greater than the number of sub-sections, then an empty string will be returned. Example: Rieger,Bob <N> = 0 would cause Rieger to be returned. <N> = 1 would cause Bob to be returned.
SPLIT-SEMICOLON	<N>	The data will be split into sub-sections based on a semicolon delimiter, and the Nth sub-section will be returned. N begins at 0. If N is greater than the number of sub-sections, then an empty string will be returned. Example: Rieger;Bob <N> = 0 would cause Rieger to be returned. <N> = 1 would cause Bob to be returned.
Split-SP	<N>	The data will be split into sub-sections based on a space delimiter, and the Nth sub-section will be returned. N begins at 0. If N is greater than the number of sub-sections, then an empty string will be returned. Example: Bob Rieger <N> = 0 would cause Bob to be returned. <N> = 1 would cause Rieger to be returned.
UPPER		The import value is converted to upper case before being stored.
Y/N		If the import value is 0, "N" will be stored. Otherwise, "Y" will be stored.
yy		If the import value is blank, 2 spaces will be stored. If the import value is non-blank, it is assumed to be a date value and will be stored as a number that represents the two-digit Year.
TO-JSON	Optional list of incoming CSV column names, comma delimited	If the parameter is blank, then the entire incoming record is converted to a VB.net Dictionary(of String,String) object type, converted to JSON, and stored as a JSON string in the target column. The column names used in the dictionary are the incoming CSV column names of the incoming record. If the parameter is populated, it is interpreted as a comma-delimited list of incoming CSV column names to be turned into a dictionary object and converted to a JSON string, before being stored in the target column. Even though the resulting JSON is converted from a VB.net Dictionary(Of String,String) type, it can be recovered as an array of simple objects with the two properties Key and Value in environments other than dot net. Key is the name of the CSV column and Value is the value.
NORMALIZE	LIB:<library code> RET:<column to return>	(Make sure there is a space between the library code and “RET:”) Normalizes the incoming value against one of the defined normalization libraries and returns the normalized value to be stored in the target column. If no match is made to the normalization library, an empty string is returned. The empty string will be converted to a data type that matches the target column, so if the target column is a numeric type, 0 will be stored. NOTE: You must supply the necessary inputs for the library to make a match. Where the library expects a single input, such as the ENVIRON library, then the incoming CSV column that carries the un-normalized value for Environment is set to be formatted using NORMALIZE and the result will then be stored to the Environment target column. However, for some libraries matches are made using several expected incoming columns – and values must be supplied for those incoming columns if a match is to be expected. Consult the Normalization Libraries help topic for Material Columns (required) and make sure you supply incoming data to those columns.

Dual-Operand Import Transformations

Some input transformations accept two operands, in which case the first operand is expected to be placed in the Source Field column while the second operand is expected to be placed in the Constant column. The second operand supports tag replacement, including the CSV tag, which dynamically refers to a field value in the current import row. This feature allows values to be computed from two fields from the current import row, and then stored in the selected Destination Field. As an example, if an import file were to contain a processor count and a cores-per-processor amount, a multiply transformation can be used to store the product of the two values into the total cores column.

Example:

As an example, the following import transformation specifies that the Cores field of the MIE-Assets table be populated with the product of the “Processors” and “CoresPerProc” fields in the current import row.

Source Field	Constant	Transform	Destination Field
Processors	[%CoresPerProc%]	ARITH-Multiply	MIE-Assets-UpdateOnly: Cores

Note the syntax for the “constant” parameter… [%CoresPerProc%], where CoresPerProc refers to the CSV field (not to a database table column).

The following table documents the dual-operand transformations as well as the parameters expected in the Source Field and Constant columns.

Transformation	Operand 1 (entered into the Source Field)	Operand 2 (entered into the Constant Column)	Description
ARITH-Add	Column name of first addend	Second addend. May be a constant. Supports tag replacement.	Adds the value of operands
ARITH-Subtract:1-2	Column name of minuend	Column name of subtrahend. May be a constant. Supports tag replacement.	Subtracts the second operand from the first operand.
ARITH-Subtract:2-1	Column name of subtrahend.	Column name of minuend. May be a constant. Supports tag replacement.	Subtracts the first operand from the second operand.
ARITH-Multiply	Column name of first factor	Column name of second factor. May be a constant. Supports tag replacement.	Multiplies the operands
ARITH-Divide:1/2	Column name of dividend	Column name of divisor. May be a constant. Supports tag replacement.	Divides the first operand by the second operand.
ARITH-Divide:2/1	Column name of divisor	Column name of dividend. May be a constant. Supports tag replacement.	Divides the first operand by the second operand.

Transform on Import

Transform on Import