Working with Data Formats

Astera Data Integrator supports a variety of formats for each data type. For example, for Dates, you can specify the date as April 12 or 12-Apr-08. Data Formats can be configured independently for source and for destination, giving you the flexibility to correctly read source data and change its format as it is transferred to destination.

If you are transferring from a flat file (for example, Delimited or Fixed-Width), you can specify the format of a field so that the system can correctly read the data from that field.

If you do not specify a data format, the system will try to guess the correct format for the field. For example, Astera is able to correctly interpret any of the following as a Date:

• April 12

• 12-Apr-08

• 04-12-2008

• Saturday, 12 April 2008

and so on.

AsteraData Integrator comes with a variety of pre-configured formats for each supported data type. These formats are listed in the Sample Formats section below. You can also create and save your own data formats.

To open the Data Formats screen, click icon on the Toolbar.

To select a data format for a source field, go to Source Fields and expand the Format dropdown menu next to the appropriate field.

To select a data format for a destination field, go to Destination Fields and expand the Format dropdown menu next to the appropriate field.

Sample Formats

Dates:

Format	Sample Value
dd-MMM-yyyy	12-Apr-2008
yyyy-MM-dd	2008-04-12
dd-MM-yy	12-04-08
MM-dd-yyyy	04-12-2008
MM/dd/yyyy	04/12/2008
MM/dd/yy	04/12/08
dd-MMM-yy	12-Apr-08
M	April 12
D	12 April 2008
mm-dd-yyyy hh:mm:ss tt	04-12-2008 11:04:53 PM
M/d/yyyy hh:mm:ss tt	4/12/2008 11:04:53 PM

Booleans:

Format	Sample Value
Y/N	Y/N
1/0	1/0
T/F	T/F
True/False	True/False

Integers:

Format	Sample Value
######	123456
####	1234
####;0;(####)	-1234
.##%;0;(.##%)	123456789000%
.##%;(.##%)	1234567800%
$###,###,###,###	$1,234,567,890,000
$###,###,###,##0	$1,234,567,890,000
###,###	123450
#,#	1,000
##.00	35

Real Numbers:

Format	Sample Value
###,###.##	12,345.67
##.##	12.34
$###,###,###,###	$1,234,567,890,000
$###,###,###,##0	$1,234,567,890,000
.##%;(.##%);	.1234567800%
.##%;0;(.##%)	.12345678900%

Numeric Format Specifiers

Format specifier	Name	Description
0	Zero placeholder	If the value being formatted has a digit in the position, where the '0' appears in the format string, then that digit is copied to the result string; otherwise, a '0' appears in the result string. The position of the leftmost '0' before the decimal point and the rightmost '0' after the decimal point determines the range of digits that are always present in the result string.The 00 specifier causes the value to be rounded to the nearest digit preceding the decimal, where rounding away from zero is always used. For example, formatting 34.5 with 00 would result in the value 35.
#	Digit placeholder	If the value being formatted has a digit in the position where the '#' appears in the format string, then that digit is copied to the result string. Otherwise, nothing is stored in that position in the result string.Note that this specifier never displays the '0' character if it is not a significant digit, even if '0' is the only digit in the string. It will display the '0' character if it is a significant digit in the number being displayed.The ## format string causes the value to be rounded to the nearest digit preceding the decimal, where rounding away from zero is always used. For example, formatting 34.5 with ## would result in the value 35.
.	Decimal Point	The first '.' character in the format string determines the location of the decimal separator in the formatted value; any additional '.' characters are ignored.
,	Thousand separator and number scaling	The ',' character serves as both a thousand-separator specifier and a number scaling specifier.Thousand separator specifier: If one or more ',' characters is specified between two digit placeholders (0 or #) that format the integral digits of a number, a group separator character is inserted between each number group in the integral part of the output.Number scaling specifier: If one or more ',' characters is specified immediately to the left of the explicit or implicit decimal point, the number to be formatted is divided by 1000 each time a number scaling specifier occurs. For example, if the string 0,, is used to format the number 100 million, the output is 100.
%	Percentage placeholder	The presence of a '%' character in a format string causes a number to be multiplied by 100 before it is formatted. The appropriate symbol is inserted in the number itself at the location where the '%' appears in the format string.
E0E+0E-0e0e+0e-0	Scientific notation	If any of the strings E, E+, E-, e, e+, or e- are present in the format string and are followed immediately by at least one '0' character, then the number is formatted using scientific notation with an 'E' or 'e' inserted between the number and the exponent. The number of '0' characters following the scientific notation indicator determines the minimum number of digits to output for the exponent. The E+ and e+ formats indicate that a sign character (plus or minus) should always precede the exponent. The E, E-, e, or e- formats indicate that a sign character should only precede negative exponents.
'ABC'ABC	Literal string	Characters enclosed in single or double quotes are copied to the result string, and do not affect formatting.
;	Section separator	The ';' character is used to separate sections for positive, negative, and zero numbers in the format string. If there are two sections in the custom format string, the leftmost section defines the formatting of positive and zero numbers, while the rightmost section defines the formatting of negative numbers. If there are three sections, the leftmost section defines the formatting of positive numbers, the middle section defines the formatting of zero numbers, and the rightmost section defines the formatting of negative numbers.
Other	All other characters	Any other character is copied to the result string, and does not affect formatting.