Show/Hide All

Data type validation for import jobs

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.

 String

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.

 Example 1 - 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

 

 Example 2 - How imported string data is formatted

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

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.

 Example 1 - How imported numeric data is imported

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

 

 Example 2 - How imported numeric data is formatted

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

 

 Date

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.

 Example - How imported dates are formatted

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

 

 Yes/No

These fields must contain either Y or N. No other values are allowed.