Data type conversions from Microsoft Excel to IBM DataStage

Before the Excel stage writes data that is extracted from Microsoft Excel to the output link, the data is converted to IBM® DataStage® data types.

The following table shows the mapping between Microsoft Excel data types and IBM DataStage data types.

Note: The Excel stage maps the data type conversions from Microsoft Excel to IBM DataStage only when the Excel stage reads records from the Microsoft Excel data source.

Table 1. Mapping between Microsoft Excel cell value data types IBM DataStage data types
Microsoft Excel cell data type	DataStage data type
Blank	Integer data types BigInt Integer SmallInt TinyInt Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar Fraction data types Double Float Real Decimal data types Decimal Numeric Date and time data type Date Time Timestamp
Boolean	Integer data types BigInt Integer SmallInt TinyInt Note: Maps TRUE: 1, FALSE: 0 Text data types Char VarChar LongVarChar Note: Maps TRUE: "true", FALSE: "false" National language text data types NChar NVarChar LongNVarChar Note: Maps TRUE: "true", FALSE: "false"
Error	Text data types Char VarChar LongVarChar Note: String expression of the error. For example, #NAME? National language text data types NChar NVarChar LongNVarChar Note: String expression of the error. For example, #NAME?
Numeric	Integer data types BigInt Integer SmallInt TinyInt Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar Fraction data types Double Float Real Decimal data types Decimal Numeric Date and time data type Date Time Timestamp
String	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar Date and time data type Date Time Timestamp

Table 2. Microsoft Excel other cell information data types and IBM DataStage data types
Microsoft Excel other cell information data types	IBM DataStage data types
Formula	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar
Comment	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar
Author of comment	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar
Hyperlink type	Integer data types BigInt Integer SmallInt TinyInt Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar
Hyperlink address	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar
Hyperlink label	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar

Table 3. Mapping between Microsoft Excel cell value data types and IBM DataStage data types
Microsoft Excel cell value data types	IBM DataStage data types
File name	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar
File Path	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar
File Size	Integer data types BigInt Integer SmallInt TinyInt Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar
Last Modified Date	Text data types Char VarChar LongVarChar Note: String expression in yyyy-mm-dd format National language text data types NChar NVarChar LongNVarChar Note: String expression in yyyy-mm-dd format Date and time data type Date Time Timestamp

Table 4. Mapping between Microsoft Excel document properties and IBM DataStage data types
Microsoft Excel document properties	IBM DataStage data types
Authors	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar
Document Comments	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar
Content Creation Date	Text data types Char VarChar LongVarChar Note: String expression in yyyy-mm-dd format National language text data types NChar NVarChar LongNVarChar Note: String expression in yyyy-mm-dd format Date and time data type Date Time Timestamp
Key Words	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar
Revision Number	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar
Subject	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar
Title	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar
Company	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar
Category	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar
Manager	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar

Table 5. Mapping between Microsoft Excel custom property and IBM DataStage data types
Mapping between Microsoft Excel custom property	IBM DataStage data types
Text	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar
Date	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar Date and time data type Date Time Timestamp
Number	Integer data types BigInt Integer SmallInt TinyInt Note: If the value is an integer. Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar Fraction data types Double Float Real Decimal data types Decimal Numeric
Boolean	Integer data types BigInt Integer SmallInt TinyInt Note: Maps TRUE: 1, FALSE: 0 Text data types Char VarChar LongVarChar Note: Maps TRUE: "true", FALSE: "false" National language text data types NChar NVarChar LongNVarChar Note: Maps TRUE: "true", FALSE: "false"

Table 6. Mapping Microsoft Excel sheet information with IBM DataStage data types
Microsoft Excel sheet information	IBM DataStage data types
Sheet Name	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar
Header	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar Note: For both text data types and National language text data types, Microsoft Excel supports special commands represented by single letter with a leading ampersand "&" in Microsoft Excel header and footer. The Excel stage does not convert those letters and just preserve them in the extracted text. Refer to http://msdn.microsoft.com/en-us/library/dd773041%28v=office.12%29.aspx for more information about the special commands.
Footer	Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar Note: For both text data types and National language text data types, Microsoft Excel supports special commands represented by single letter with a leading ampersand "&" in Microsoft Excel header and footer. The Excel stage does not convert those letters and just preserve them in the extracted text. Refer to http://msdn.microsoft.com/en-us/library/dd773041%28v=office.12%29.aspx for more information about the special commands.

Table 7. Mapping between Microsoft Excel row information and their equivalent IBM DataStage data types
Microsoft Excel row information	IBM DataStage Data types
Row Number	Integer data types BigInt Integer SmallInt TinyInt Text data types Char VarChar LongVarChar National language text data types NChar NVarChar LongNVarChar
Is Hidden	Integer data types BigInt Integer SmallInt TinyInt Note: Maps TRUE: 1, FALSE: 0 Text data types Char VarChar LongVarChar Note: Maps TRUE: "true", FALSE: "false" National language text data types NChar NVarChar LongNVarChar Note: Maps TRUE: "true", FALSE: "false"