Working with Data Formats

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.

E0

E+0

E-0

e0

e+0

e-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.

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.