Excel File Destination¶
Excel File¶
Centerprise Excel File Destination provides the functionality to write data to Microsoft Excel workbook. Excel File Destination does not need Microsoft Excel to be installed on the machine. Excel File Destination enables you to specify worksheet and starting cell where the write actually begins.
Appending to Existing Files and Worksheets¶
You can choose to append to an existing worksheet or create a new worksheet. You can also choose to write to an existing file or create a new file. If you choose to create a new file, any existing file will be overwritten.
Writing to Multiple Excel Worksheets¶
Centerprise enables you to write to multiple Excel worksheets from within a single dataflow. When you specify the same file in multiple Excel destinations, Centerprise creates a single file with multiple worksheets and writes all data to that file.
Steps¶
Adding an Excel file destination object allows you to write to an Excel file. An example of what an Excel file destination object looks like is shown below.
To configure the properties of an Excel Destination object after it was added to the dataflow, right-click on it and select Properties from the context menu.
NOTE: To ensure that your dataflow is runnable on a remote server, please avoid using local paths. Using UNC paths is recommended.
Field Layout¶
The Field Layout screen is available in the properties of most objects on the dataflow to help you specify the fields making up the object. The table below explains the attributes you can set in the Field Layout screen.
| Attribute | Description |
|---|---|
| Name | The system pre-fills this item for you based on the field header. Field names do not allow spaces. Field names are used to refer to the fields in the Expression Builder or tools where a field is used in a calculation formula. |
| Header | Represents the field name specified in the header row of the file. Field headers may contain spaces. |
| Data Type | Specifies the data type of a field, such as Integer, Real, String, Date, or Boolean. |
| Format | Specifies the format of the values stored in that field, depending on the field’s data type. For example, for dates you can choose between DD-MM-YY, YYYY-MM-DD, or other available formats. |
| Start Position | Specifies the position of the field’s first character relative to the beginning of the record.Note: This option is only available for fixed length layout type. |
| Length | Specifies the maximum number of characters allotted for a value in the field. The actual value may be shorter than what is allowed by the Length attribute.Note: This option is only available for fixed length and database layout types. |
| Column Name | Specifies the column name of the database table.Note: This option is only available in database layout. |
| DB Type | Specifies the database specific data type that the system assigns to the field based on the field's data. Each database (Oracle, SQL, Sybase, etc) has its own DB types. For example, Long is only available in Oracle for data type string.Note: This option is only available in database layout. |
| Decimal Places | Specifies the number of decimal places for a data type specified as real.Note: This option is only available in database layout. |
| Allows Null | Controls whether the field allows blank or NULL values in it. |
| Default Value | Specifies the value that is assigned to the field in any one of the following cases:- The source field does not have a value- The field is not found in the source layout- The destination field is not mapped to a source fieldNote: This option is only available in destination layout. |
| Sequence | Represents the column order in the source file. You can change the column order of the data being imported by simply changing the number in the sequence field. The other fields in the layout will then be reordered accordingly. |
| Description | Contains information about the field to help you remember its purpose. |
| Alignment | Specifies the positioning of the field’s value relative to the start position of the field. Available alignment modes are LEFT, CENTER, and RIGHT.Note: This option is only available for fixed length layout type. |
| Primary Key | Denotes the primary key field (or part of a composite primary key) for the table.Note: This option is only available in database layout. |
| System Generated | Indicates that the field will be automatically assigned an increasing Integer number during the transfer.Note: This option is only available in database layout. |
The table below provides a list of all the attributes available for a particular layout type.
| Layout Type | Attributes Available |
|---|---|
| Source Delimited file and Excel worksheet | Name, Header, Data type, Format |
| Source Fixed Length file | Name, Header, Data type, Format, Start position, Length |
| Source Database Table and SQL query | Column name, Name, Data type, DB type, Length, Decimal places, Allows null |
| Destination Delimited file and Excel worksheet | Name, Header, Data type, Format, Allows null, Default value |
| Destination Fixed Length file | Sequence, Name, Header, Description, Data type, Format, Start position, Length, Allows null, Default value, Alignment |
| Destination Database Table | Column name, Name, Data type, DB type, Length, Decimal places, Allows null, Primary key, System generated |