Working with Data FormatsΒΆ
Centerprise 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, Centerprise 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
Centerprise Data 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. |