For information about data type validation, refer to the appropriate section:
Data type validation for import jobs in the Material Requirements Planning and Work Order modules
Each data item being imported is checked to ensure that the data is consistent with the data type defined for the item. The import data is also processed into a format appropriate for that data type. The validation process is performed for each of the following data types.
Alphanumeric characters are allowed for an account-type field. Import data for an account-type field is processed in accordance with the import mask specified on the Import Job Maintenance Data tab. If alphabetical data is imported, the alphabetical characters are left justified in the mask, and if the mask is smaller than the actual field size, the character positions to the right of the mask are zero-filled. If numeric data is imported, the digits are right justified in the mask and the unused portion of the mask is zero-filled. If the mask is smaller than the actual field size, the character positions to the right of the mask are zero-filled.
The following illustration shows how incoming data is formatted if import mask ######### is specified at an account-type field with a field size of nine characters.
Import Data
Formatted Data
123456789
123456789
1234
000001234
2A00
2A00
ABC
ABC
The following illustration shows how incoming data is formatted if import mask #### is specified for an account-type field with a field size of nine characters.
Import Data
Formatted Data
1234
123400000
12
001200000
ABC
ABC_00000
A20
A20_00000
Note The underscore character (_) used in these examples represent a blank character.
All date-type fields must contain the date in a consistent format specified for the field. You can select from one of the 17 predefined date formats. The selected format defines where the month, date, and year information is stored. Each component of the date is verified separately (that is, the month must be a number from 1 through 12 or the first three characters of the month). The date must be a number from 1 through 31. The year must be a number between 00 and 99 when the two-character year is used, or a number between 1800 and 2100 when the four-character year is used.
Note For file types Excel (.xls) and Lotus (.wk4 and .wks), the date format selected is not important; however , when the date is a text-type field, the date format must have slash separators and the date mask must be con sistent with the format of the text date being imported. If there are no separators in the date (for example, MMDDYY), the file extension must not be .xls, .wks, or .wk4. Instead, use the .csv or .txt file extension.
The following illustration shows some supported date formats and how the date of May 31, 2003 must be stored in the import file for each corresponding import mask used.
Import Data
Formatted Data
MMDDYY
053103
MMDDYYYY
05312003
YYMMDD
030531
MMMDDYY
MAY3103
Numeric data items must consist of numeric characters, a period, and a minus sign. Only a single period is allowed to designate the decimal point. A minus sign at the beginning or end of the field is also allowed. A number enclosed in parentheses, or ending with the letters CR, is considered to be a negative number. A number ending with the letters DR is considered to be a positive number. Any commas and blank characters in a numeric field are ignored. The numeric data is also checked to ensure that it does not exceed the numeric mask assigned to that field. The numeric mask information is provided on theData Dictionary Listing.
Alphanumeric characters are allowed for a string-type field. Any characters beyond the defined field length are ignored.
Phone-type fields must contain the phone number in one of four supported formats.
- AAAXXXNNNN
- AAA-XXX-NNNN
- (AAA) XXX-NNNN
- XXX-NNNN
The area code (AAA), prefix (XXX), and number (NNNN) can consist of any combination of alphanumeric characters . The correct length to be entered is determined by the actual format used in the source file. The length must be specified as 8 if the XXX-NNNN format is used, 10 if the AAAXXXNNNN format is used, 12 if the AAA-XXX-NNNN format is used, or 14 if the (AAA) XXX-NNNN format is used.
The following illustration shows supported phone number formats.
Import Data
Formatted Data
8
850-0111
10
7148500111
12
714-850-0111
14
(714) 850-0111
These fields must contain either Y or N. No other values are allowed.
Data type validation for import jobs in modules other than Material Requirements Planning and Work Order
Each data item being imported is checked to ensure that the data is consistent with the data type defined for the item. The import data is also processed into a format appropriate for that data type. The validation process is performed for each of the following data types.
For these modules, the data type in the dictionary is limited to String or Numeric. The dictionary supports four format types: ZEROFILL, ALPHANUM, CHARNUM, and MASTERNUM.
If the data type is String, the following validation rules apply:
ZEROFILL: Right justify and zero-fill, based on dictionary maximum length for the field.
ALPHANUM: If numeric, right justify and zero-fill, based on dictionary maximum length for the field; otherwise, each character must be 0-9, A-Z, a-z, or a blank. Blanks are stripped from the right end.
CHARNUM: Same as ALPHANUM, except that in addition, all printable characters are allowed from decimal (.) to tilde (~) in the ASCII collating sequence.
MASTERNUM: Same as CHARNUM, except that dash (-) is not allowed.
The following examples show how imported string data is formatted.
The following illustration shows how incoming string data is formatted if the format type is ZEROFILL and the dictionary length is 9.
Import Data
Formatted Data
123456789
123456789
1234
000001234
2A00
fail
ABC
fail
The following illustration shows how incoming string data is formatted if the format type is ALPHANUM and the dictionary length is 9.
Import Data
Formatted Data
1234
000001234
12
000000012
ABC
ABC
aBc
aBc
A20
A20
A 20
A 20
A-20
fail
12.34
fail
Numeric data items must consist of numeric characters, a period, and a minus sign. Only a single period is allowed to designate the decimal point. A minus sign at the beginning or end of the field is also allowed. A number enclosed in parentheses, or ending with the letters CR, is considered to be a negative number. A number ending with the letters DR is considered to be a positive number. Any commas and blank characters in a numeric field are ignored. The numeric data is also checked to ensure that it does not exceed the numeric mask assigned to that field.
The following illustration shows how imported numeric data is processed by the Visual Integrator module.
Import Data
Formatted Data
10,530.51
10530.51
900.00-
-900.00
(5100.00)
-5100.00
20,100CR
-20100.00
The following illustration shows how imported numeric data is formatted if the mask is ###,###.00-.
Import Data
Formatted Data
1234567
fail
123456.78
123,456.78
-123456.78
123,456.78-
1234
1,234.00
All date-type fields belong to a class called DATE and must contain the date in a consistent format specified for the field. You can select from one of the 10 predefined date formats. The selected format code defines where the month, date, and year information is stored. Each component of the date is verified separately (that is, the month must be a number from 1 through 12 or the first three characters of the month). The date must be a number from 1 through 31. The year must be a number between 00 and 99 when the two-character year is used, or a number between 1800 and 2099 when the four-character year is used.
Note For file types Excel (.xls) and Lotus (.wk4 and .wks), the date format selected is not important; however , when the date is a text-type field, the date format must have slash separators and the date mask must be con sistent with the format of the text date being imported. If there are no separators in the date (for example, MMDDYY), the file extension must not be .xls, .wks, or .wk4. Instead, use the .csv or .txt file extension.
The following illustration shows all supported date formats and how the date of May 31, 2010 must be stored in the import file for each corresponding import mask used.
Format Code
Import Mask
Import Data
1
YYYYMMDD
20100531
2
YYMMDD
100531
3
MMDDYY
053110
4
MMDDYYYY
05312010
5
DDMMYY
310510
6
DDMMYYYY
31052010
7
M/D/Y
05/31/10 or 5-31-2010 or May/31/2010, etc.
8
D/M/Y
31-5-10 or 31/05/2010 or 31/May/10, etc.
9
Y/M/D
2010/05/31 or 10-5-31 or 2010.may.31, etc.
10
MAS90
?:0531
These fields must contain either Y or N. No other values are allowed.