Excel Workbook Destination
Last updated
Last updated
The Excel Workbook Destination object in Astera Data Stack provides the functionality to write data to Microsoft Excel workbooks. An important thing to note here is that it is not necessary to have Microsoft Excel installed on the machine for the Excel destination object in Astera to work.
The feature gives you the option to specify the worksheet and the starting cell where the data write begins.
To get the object from the Toolbox, go to Toolbox > Destinations > Excel Workbook Destination. If you are unable to see the Toolbox, go to View > Toolbox or press Ctrl + Alt + X.
Drag and drop the Excel Workbook Destination object onto the designer.
The dragged destination object is empty right now. This is because the data fields are not mapped to it yet. In this case, we will use a simple source to excel destination mapping scenario as an example.
Configure the source object and place it onto the designer next to the Excel Workbook Destination object.
Note: We are using a sample table containing Customers data from an SQL database.
Now map the data fields from the source object to the destination object. Mapping can be done in the following ways:
i. Dragging and dropping the parent node of the source object onto the parent node of the destination object for auto-mapping the layout.
ii. Creating a map from the source parent node to the destination parent node.
iii. Directly writing the fields in the source layout to an Excel Destination through the source context menu of its parent node.
The fields are now mapped.
To configure the Excel Workbook Destination object, right-click on the header, select Properties from the context menu and a dialog box will open.
Provide the File Path. This is where the Excel destination file will be saved.
The dialog box has some other configuration options. Let’s go over these options:
If the First Row Contains Header, check this option to read headers from the source file.
The Worksheet field can be used to specify the name of a worksheet for either overwriting the data in an already existing worksheet or adding a new worksheet.
Choose Append to File (If Exists) to append to an existing file or create a new file. Creating a new file will overwrite any existing file.
Check on Write to Multiple Files for the data to be saved to multiple files instead of one single file. This can be done within a single dataflow through the destination object and supporting transformations.
Once the data reading options have been specified on this screen, click Next.
The next window is the Layout Builder. On this window, the layout of the Excel Destination file can be modified.
To add a new field to the layout, go to the last row of the layout (Name column), which will be blank and double-click on it, and a blinking text cursor will appear. Type in the name of the field to be added and select the subsequent properties for it. A new field will be added to the source layout.
Note: Adding a new field (Email) to the layout.
To delete a field from the layout, click on the serial column of the row that is to be deleted. The selected row will be highlighted in blue.
Note: Deleting the Fax field from the layout.
Right-click on the highlighted line, a context menu will appear which will have the option to Delete.
Selecting Delete will delete the entire row.
The field is now deleted from the layout and won’t appear in the output.
To change the position of any field and move it below or above another field in the layout, select the row and use the Move up/Move down keys.
Note: Find the Move up/Move down icons on the top left of the builder.
For example: To move the Country field right below the Region field, select the row and use the Move up key from the 9th row to the 8th row.
The row is now moved from the 9th position to the 8th position.
Once the object layout is configured, click Next. A new window will appear, Config Parameters, which allows us to further configure and define the parameters for the Excel destination file.
Parameters can provide easier deployment of flows by eliminating hardcoded values and provide an easier way of changing multiple configurations with a simple value change.
Note: Parameters left blank will use their default values assigned on the properties page.
A General Options window will appear. On this window:
Comments can be added.
General Options are given, which relate to the processing of records in the destination file.
Click OK.
The ExcelDest object is now configured according to the changes that were made in the properties window.
Note: The changes that were made in this case are:
Added a new field, Email in the layout.
Moved the Country field below the Region field.
The Excel Workbook Destination object is successfully configured, and the destination file can now be created by running the dataflow.
